----- Original Message -----
From: "Louis-David Mitterrand" <vindex+lists-pgsql-sql@apartia.org>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, March 03, 2010 3:38 PM
Subject: Re: [SQL] check constraint on multiple tables?
> On Wed, Mar 03, 2010 at 07:29:22AM -0800, Richard Broersma wrote:
>> On Wed, Mar 3, 2010 at 7:19 AM, Louis-David Mitterrand
>> <vindex+lists-pgsql-sql@apartia.org> wrote:
>>
>> > CREATE TABLE cabin_type (
>>
>> > CREATE TABLE cabin_category (
>>
>> > CREATE TABLE cabin (
>>
>> I'm just curious about a few things.
>>
>> 1) What is the difference between a cabin_type and a cabin_category.
>
> A cabin_type is: large suite, junior suite, balcony cabin, interior, etc.
>
> A cabin_category is, for say a "balcony cabin", on which deck it is
> located (price increases as the deck is higher).
>
>> 2) Does each ship have an exclusive set of cabin_types that no other
>> ship can have? The table definitions imply that this is so.
>
> Each ship is different and has specific cabin types and categories. Of
> course there is some overlap between ships but I thought it simpler (or
> more elegant) to use that hierarchy. Maybe my schema is wrong?
>
>> I'm just guessing here since I don't really understand the
>> relationships involved in a ship's cabins. However, I would expect
>> that a cabin should be directly related to a ship. Each cabin is
>> defined by a category according the set in the cabin_category table.
>
> I could add an id_ship to 'cabin' but that would make two (potentialy
> conflicting) relations to 'ship'.
As your table names seem to imply, type and category are cabin's
characteristics , not ship characteristics.
Am I right?
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.
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" ?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...
Best,
Oliveiros d'Azevedo Cristina