Обсуждение: check constraint on multiple tables?
Hi, I've got this chain of tables: ship --> (id_ship) --> cabin_type --> (id_cabin_type) --> cabin_category--> (id_cabin_category) --> cabin The 'cabin' table has (cabin_number, id_cabin_category ref. cabin_category) How can I guarantee unicity of cabin_number per ship? For now I added a unique(cabin_number,id_cabin_category) but this does not guarantee unicity for (cabin_number,ship.id_ship). What is the best solution? Adding an id_ship to 'cabin'? Or check'ing with a join down to 'ship'? (if possible). Thanks,
On Wed, Mar 3, 2010 at 7:02 AM, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote: > What is the best solution? Adding an id_ship to 'cabin'? Or check'ing > with a join down to 'ship'? (if possible). Can you post simplified table definitions for the relations involved? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Wed, Mar 03, 2010 at 07:14:29AM -0800, Richard Broersma wrote: > On Wed, Mar 3, 2010 at 7:02 AM, Louis-David Mitterrand > <vindex+lists-pgsql-sql@apartia.org> wrote: > > > > What is the best solution? Adding an id_ship to 'cabin'? Or check'ing > > with a join down to 'ship'? (if possible). > > Can you post simplified table definitions for the relations involved? Sure, here they are: CREATE TABLE ship ( id_ship serial primary key, ship_name text unique not null ); CREATE TABLE cabin_type ( id_cabin_type serial primary key, id_ship integer references ship, cabin_type_name text, cabin_type_code text,unique(cabin_type_code, id_ship) ); CREATE TABLE cabin_category ( id_cabin_category serial primary key, id_cabin_type integer references cabin_type, cabin_cat_nametext, cabin_cat_code text,unique(cabin_cat_code, id_cabin_type) ); CREATE TABLE cabin ( id_cabin serial primary key, id_cabin_category integer references cabin_category, cabin_numberinteger not null,unique(id_cabin_category, cabin_number) );
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. 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. 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. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
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'.
On Wed, Mar 3, 2010 at 7:38 AM, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote: > 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 probably ignorant about ships, but are ships like air planes in that all ships belong to a similar class/construction? i.e. all Bowing 747 are essentially configured that same. Can you define ship classes that each ship belongs? You describe cabin_category as if it is actually an area of this ship, would all ships of a class have nearly identical ship_areas/cabin_categories? > I could add an id_ship to 'cabin' but that would make two (potentialy > conflicting) relations to 'ship'. It true that more refinement is necessary. As you've mentioned that this hierarchy will have overlap, this is an indication of a violation of the second normal form (I believe). -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Hello, I would have designed as ship > cabin (PK of ship_id, Cabin_id) And a separate chain of cabin_type > cabin_category > cabin Type, and category are group classifiers and shouldn't be used to define the uniqueness of a cabin. Take an example where the cabin category and type are defined globally for the entire fleet. Currently you'll have to duplicatethe type, category defintions for each ship. Doug -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Louis-David Mitterrand Sent: Wednesday, March 03, 2010 9:02 AM To: pgsql-sql@postgresql.org Subject: [SQL] check constraint on multiple tables? Hi, I've got this chain of tables: ship --> (id_ship) --> cabin_type --> (id_cabin_type) --> cabin_category--> (id_cabin_category) --> cabin The 'cabin' table has (cabin_number, id_cabin_category ref. cabin_category) How can I guarantee unicity of cabin_number per ship? For now I added a unique(cabin_number,id_cabin_category) but this does not guarantee unicity for (cabin_number,ship.id_ship). What is the best solution? Adding an id_ship to 'cabin'? Or check'ing with a join down to 'ship'? (if possible). Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
----- 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
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.
Hey Louis, Ship rooms are just like Hotel rooms. There are lots of ways to describe. But there needs to be some consistency betweenthe 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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Louis-David Mitterrand Sent: Wednesday, March 03, 2010 10:07 AM To: pgsql-sql@postgresql.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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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 onthe fly, - each ship class (a cruise line has several) has its own particular types (Celebrity Xpedition is the only one with "Xpeditionsuite" 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.
On Wed, Mar 03, 2010 at 10:13:48AM -0600, Little, Douglas wrote: > 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. Not so simple, cruise lines like to slice and dice (segment in marketing speak) their offerings by creating ship-specific cabin types with (ever so slightly) different features and prices.
Louis, Interesting discussion. Always fun to think about real world stuff. We have a similar problem for comparing hotel rooms. So the issue is that you aren't originating the data, just classifying it. I'd move toward a scheme where you reclassify the line marketing speak to common lay terms. You're trying to help consumerscompare. Exactly what the marketers don't want them to do. After all a silk purse is just a sow's ear without marketing. :) Doug -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Louis-David Mitterrand Sent: Wednesday, March 03, 2010 10:18 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] 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 onthe fly, - each ship class (a cruise line has several) has its own particular types (Celebrity Xpedition is the only one with "Xpeditionsuite" 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
On Wed, Mar 03, 2010 at 10:29:44AM -0600, Little, Douglas wrote: > Louis, > Interesting discussion. Always fun to think about real world stuff. Indeed. > We have a similar problem for comparing hotel rooms. > So the issue is that you aren't originating the data, just classifying it. > I'd move toward a scheme where you reclassify the line marketing speak > to common lay terms. You're trying to help consumers compare. > Exactly what the marketers don't want them to do. I'm leaning towards exactly that: letting the data settle, spot overlap and general trends, reclassify. > After all a silk purse is just a sow's ear without marketing. > :) True but cruise customers buy into that marketing and insist on those slight distinctions between almost identical cabins. It's all a question of status and it's what cruise lines sell. Thanks,