Re: check constraint on multiple tables?

On Wed, Mar 03, 2010 at 04:05:43PM -0000, Oliveiros wrote:
> 
> As your table names seem to imply, type and category are cabin's
> characteristics , not ship characteristics.
> Am I right?

Yes and no :) 

- I'm scanning cruise line web sites with a spider to collect prices so I'm building cabin_type's and cabin_category's on the fly,
- each ship class (a cruise line has several) has its own particular types (Celebrity Xpedition is the only one with "Xpedition suite" type, etc.)

> As Richard pointed out, maybe you could add a relationship between
> cabin and ship and drop the relationship between ship and
> cabin_category you now have
> Then you could add that uniqueness restriction.

That's one option.

> Also, the relationship between type and category is one to many ? Or
> can it be many to many? Put other way, is this overlap between the
> categories that belong to different "types" ?

One cabin_type to many cabin_category's, for example:

- "Sunset Veranda Stateroom" (type) can be on "Vista", "Panorama", etc. decks (category) with a different price,

But it's true that there is some overlap in categories between different
ships.

> If the later applies, maybe
> you could have cabin refer to both type and category tables and drop
> the relation between type and category.
> 
> The cabin table would then work as an associative table between
> category and type.
> 
> Ain't saying your schema is wrong, maybe you have strong reasons to
> do that that way, that I am not realizin by now...

You got me thinking about it. Thank you for your interesting comments.