Re: 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(at)apartia(dot)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'.