Re: check constraint on multiple tables?

Hey Louis,

Ship rooms are just like Hotel rooms. There are lots of ways to describe. But there needs to be some consistency between the classifiers for them to have any meaning.

A junior suite should mean the same thing regardless of the ship it's on.

Doug

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Louis-David Mitterrand
Sent: Wednesday, March 03, 2010 10:07 AM
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] check constraint on multiple tables?

On Wed, Mar 03, 2010 at 10:03:05AM -0600, Little, Douglas wrote:
> Hello,
>
> I would have designed as ship > cabin (PK of ship_id, Cabin_id)
> And a separate chain of cabin_type > cabin_category > cabin

Ah, now I'm having second thoughts about my schema ;)

> Type, and category are group classifiers and shouldn't be used to
> define the uniqueness of a cabin.

Yes, but some ships have quite unique cabin types and categories.

> Take an example where the cabin category and type are defined globally
> for the entire fleet. Currently you'll have to duplicate the type,
> category defintions for each ship.

Each ship is unique (more or less, a cruise line has several classes of
ships). So are its types and cats so it's not so clear cut.