From: Oliveiros <oliveiros.cristina@marktest.pt>
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: Re: check constraint on multiple tables?
Date: Wed, 3 Mar 2010 16:05:43 -0000
Message-ID: <7B7DB40901C947B688FD3BAC874E762F@marktestcr.marktest.pt> (raw)
References: <20100303150227.GA18661@apartia.fr>
<396486431003030714s48b239e2ub8b58a49b9aa29e4@mail.gmail.com>
<20100303151900.GA19735@apartia.fr>
<396486431003030729x17f921bcr7ff439113a5644ab@mail.gmail.com>
<20100303153831.GA20503@apartia.fr>
----- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@marktest.pt, vindex+lists-pgsql-sql@apartia.org
Subject: Re: check constraint on multiple tables?
In-Reply-To: <7B7DB40901C947B688FD3BAC874E762F@marktestcr.marktest.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox