pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCHECK constraint on multiple tables
21+ messages / 9 participants
[nested] [flat]
* CHECK constraint on multiple tables
@ 2009-09-14 13:24 Mario Splivalo <mario.splivalo@megafon.hr>
0 siblings, 2 replies; 21+ messages in thread
From: Mario Splivalo @ 2009-09-14 13:24 UTC (permalink / raw)
To: pgsql-sql
I have two tables, tableA and tableB:
CREATE TABLE tableA (idA integer primary key, email character varying
unique);
CREATE TABLE tableB (idB integer primary key, email character varying
unique);
Now, I want to create check constraint in both tables that would
disallow records to either table where email is 'mentioned' in other table.
If CHECK constraints supported SUBSELECTS, I could write:
ALTER TABLE tableA ADD CONSTRAINT tableA_chk CHECK
(email NOT IN (SELECT email FROM tableB));
Unfortunatley, postgres won't allow me to do so.
Now, i could create function, check_for_email, that would return TRUE if
email is mentioned in either table, and then call that function when
creating a check constraint.
Or I could add separate table, emails, like this:
CREATE TABLE emails (email_id integer primary key, email character
varying unique)
And then replace 'email' column in tables tableA and tableB with
'email_id' that would be foreign key refference to the emails table.
I could, also, write functions for inserting data to the tableA and
tableB tables.
What would be the best approach to solve the problem I have? Could I use
rules on insert to help me?
Mario
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: CHECK constraint on multiple tables
@ 2009-09-14 13:27 Ries van Twisk <pg@rvt.dds.nl>
parent: Mario Splivalo <mario.splivalo@megafon.hr>
1 sibling, 1 reply; 21+ messages in thread
From: Ries van Twisk @ 2009-09-14 13:27 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: pgsql-sql
can't you solve it creating a reference between the tables?
Ries
On Sep 14, 2009, at 8:24 AM, Mario Splivalo wrote:
> I have two tables, tableA and tableB:
>
> CREATE TABLE tableA (idA integer primary key, email character varying
> unique);
> CREATE TABLE tableB (idB integer primary key, email character varying
> unique);
>
> Now, I want to create check constraint in both tables that would
> disallow records to either table where email is 'mentioned' in other
> table.
>
> If CHECK constraints supported SUBSELECTS, I could write:
>
> ALTER TABLE tableA ADD CONSTRAINT tableA_chk CHECK
> (email NOT IN (SELECT email FROM tableB));
>
> Unfortunatley, postgres won't allow me to do so.
>
> Now, i could create function, check_for_email, that would return
> TRUE if
> email is mentioned in either table, and then call that function when
> creating a check constraint.
>
> Or I could add separate table, emails, like this:
>
> CREATE TABLE emails (email_id integer primary key, email character
> varying unique)
>
> And then replace 'email' column in tables tableA and tableB with
> 'email_id' that would be foreign key refference to the emails table.
>
> I could, also, write functions for inserting data to the tableA and
> tableB tables.
>
> What would be the best approach to solve the problem I have? Could I
> use
> rules on insert to help me?
>
> Mario
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
regards, Ries van Twisk
-------------------------------------------------------------------------------------------------
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
WebORB PostgreSQL DB-Architect
email: ries@vantwisk.nl web: http://www.rvantwisk.nl/
skype: callto://r.vantwisk
Phone: +1-810-476-4196 Cell: +593 9901 7694 SIP:
+1-747-690-5133
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: CHECK constraint on multiple tables
@ 2009-09-14 13:58 Mario Splivalo <mario.splivalo@megafon.hr>
parent: Ries van Twisk <pg@rvt.dds.nl>
0 siblings, 0 replies; 21+ messages in thread
From: Mario Splivalo @ 2009-09-14 13:58 UTC (permalink / raw)
To: Ries van Twisk <pg@rvt.dds.nl>; +Cc: pgsql-sql
How would you do it, without creating third table?
Mario
Ries van Twisk wrote:
> can't you solve it creating a reference between the tables?
>
> Ries
> On Sep 14, 2009, at 8:24 AM, Mario Splivalo wrote:
>
>> I have two tables, tableA and tableB:
>>
>> CREATE TABLE tableA (idA integer primary key, email character varying
>> unique);
>> CREATE TABLE tableB (idB integer primary key, email character varying
>> unique);
>>
>> Now, I want to create check constraint in both tables that would
>> disallow records to either table where email is 'mentioned' in other
>> table.
>>
>> If CHECK constraints supported SUBSELECTS, I could write:
>>
>> ALTER TABLE tableA ADD CONSTRAINT tableA_chk CHECK
>> (email NOT IN (SELECT email FROM tableB));
>>
>> Unfortunatley, postgres won't allow me to do so.
>>
>> Now, i could create function, check_for_email, that would return TRUE if
>> email is mentioned in either table, and then call that function when
>> creating a check constraint.
>>
>> Or I could add separate table, emails, like this:
>>
>> CREATE TABLE emails (email_id integer primary key, email character
>> varying unique)
>>
>> And then replace 'email' column in tables tableA and tableB with
>> 'email_id' that would be foreign key refference to the emails table.
>>
>> I could, also, write functions for inserting data to the tableA and
>> tableB tables.
>>
>> What would be the best approach to solve the problem I have? Could I use
>> rules on insert to help me?
>>
>> Mario
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
> regards, Ries van Twisk
>
> -------------------------------------------------------------------------------------------------
>
> tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
> WebORB PostgreSQL DB-Architect
> email: ries@vantwisk.nl web: http://www.rvantwisk.nl/ skype:
> callto://r.vantwisk
> Phone: +1-810-476-4196 Cell: +593 9901 7694 SIP:
> +1-747-690-5133
>
>
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: CHECK constraint on multiple tables
@ 2009-09-14 14:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mario Splivalo <mario.splivalo@megafon.hr>
1 sibling, 1 reply; 21+ messages in thread
From: Tom Lane @ 2009-09-14 14:18 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: pgsql-sql
Mario Splivalo <mario.splivalo@megafon.hr> writes:
> I have two tables, tableA and tableB:
> CREATE TABLE tableA (idA integer primary key, email character varying
> unique);
> CREATE TABLE tableB (idB integer primary key, email character varying
> unique);
> Now, I want to create check constraint in both tables that would
> disallow records to either table where email is 'mentioned' in other table.
Have you considered refactoring so there's only one table?
Cross-table constraints are a really bad idea unless you can express
them as foreign keys. There's a lot of "secret sauce" in the FK
mechanism that isn't available to user-written constraints.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: CHECK constraint on multiple tables
@ 2009-09-14 14:20 Mario Splivalo <mario.splivalo@megafon.hr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 21+ messages in thread
From: Mario Splivalo @ 2009-09-14 14:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
> Mario Splivalo <mario.splivalo@megafon.hr> writes:
>> I have two tables, tableA and tableB:
>> CREATE TABLE tableA (idA integer primary key, email character varying
>> unique);
>> CREATE TABLE tableB (idB integer primary key, email character varying
>> unique);
>
>> Now, I want to create check constraint in both tables that would
>> disallow records to either table where email is 'mentioned' in other table.
>
> Have you considered refactoring so there's only one table?
Unfortunately I can't do that, due to the
object-relational-mapper-wrapper-mambo-jumbo.
The only 'logical' idea that I can think of is separating emails to the
third table, and then use UNIQUE constraint on the email field on that
table, and then use FK constraint so that email fields in tables tableA
and tableB points to the email in the table emails.
Mario
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: CHECK constraint on multiple tables
@ 2009-09-14 17:00 Thomas Kellerer <spam_eater@gmx.net>
parent: Mario Splivalo <mario.splivalo@megafon.hr>
1 sibling, 0 replies; 21+ messages in thread
From: Thomas Kellerer @ 2009-09-14 17:00 UTC (permalink / raw)
To: pgsql-sql
Mario Splivalo wrote on 14.09.2009 16:20:
>> Have you considered refactoring so there's only one table?
>
> Unfortunately I can't do that, due to the
> object-relational-mapper-wrapper-mambo-jumbo.
>
You could still refactor that into one single table, then create two updateable views with the names that the dreaded OR mapper expects. That would enable you to have a proper unique check on the base table, and you OR mapper still sees two tables that it can update.
Thomas
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: CHECK constraint on multiple tables
@ 2009-09-16 03:34 Rob Sargent <robjsargent@gmail.com>
parent: Mario Splivalo <mario.splivalo@megafon.hr>
1 sibling, 0 replies; 21+ messages in thread
From: Rob Sargent @ 2009-09-16 03:34 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Mario Splivalo wrote:
> Tom Lane wrote:
>
>> Mario Splivalo <mario.splivalo@megafon.hr> writes:
>>
>>> I have two tables, tableA and tableB:
>>> CREATE TABLE tableA (idA integer primary key, email character varying
>>> unique);
>>> CREATE TABLE tableB (idB integer primary key, email character varying
>>> unique);
>>>
>>> Now, I want to create check constraint in both tables that would
>>> disallow records to either table where email is 'mentioned' in other table.
>>>
>> Have you considered refactoring so there's only one table?
>>
>
> Unfortunately I can't do that, due to the
> object-relational-mapper-wrapper-mambo-jumbo.
>
> The only 'logical' idea that I can think of is separating emails to the
> third table, and then use UNIQUE constraint on the email field on that
> table, and then use FK constraint so that email fields in tables tableA
> and tableB points to the email in the table emails.
>
> Mario
>
>
Is that wrapper hibernate by any chance? If so you might try adding a
discriminator column to the single table TL suggested and map each class
accordingly.
^ permalink raw reply [nested|flat] 21+ messages in thread
* check constraint on multiple tables?
@ 2010-03-03 15:02 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 2 replies; 21+ messages in thread
From: Louis-David Mitterrand @ 2010-03-03 15:02 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 15:14 Richard Broersma <richard.broersma@gmail.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 21+ messages in thread
From: Richard Broersma @ 2010-03-03 15:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 15:19 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 1 reply; 21+ messages in thread
From: Louis-David Mitterrand @ 2010-03-03 15:19 UTC (permalink / raw)
To: pgsql-sql
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_name text,
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_number integer not null,
unique(id_cabin_category, cabin_number)
);
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 15:29 Richard Broersma <richard.broersma@gmail.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 21+ messages in thread
From: Richard Broersma @ 2010-03-03 15:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 15:38 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 2 replies; 21+ messages in thread
From: Louis-David Mitterrand @ 2010-03-03 15:38 UTC (permalink / raw)
To: pgsql-sql
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'.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 15:57 Richard Broersma <richard.broersma@gmail.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 0 replies; 21+ messages in thread
From: Richard Broersma @ 2010-03-03 15:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 16:03 Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 21+ messages in thread
From: Little, Douglas @ 2010-03-03 16:03 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; pgsql-sql
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 duplicate the 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 16:05 Oliveiros <oliveiros.cristina@marktest.pt>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 21+ messages in thread
From: Oliveiros @ 2010-03-03 16:05 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; 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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 16:06 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
0 siblings, 1 reply; 21+ messages in thread
From: Louis-David Mitterrand @ 2010-03-03 16:06 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 16:13 Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 21+ messages in thread
From: Little, Douglas @ 2010-03-03 16:13 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; pgsql-sql
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.
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 16:17 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Oliveiros <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 21+ messages in thread
From: Louis-David Mitterrand @ 2010-03-03 16:17 UTC (permalink / raw)
To: 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 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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 16:21 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
0 siblings, 0 replies; 21+ messages in thread
From: Louis-David Mitterrand @ 2010-03-03 16:21 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 16:29 Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
parent: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 1 reply; 21+ messages in thread
From: Little, Douglas @ 2010-03-03 16:29 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; pgsql-sql
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 consumers compare. 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 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: check constraint on multiple tables?
@ 2010-03-03 16:38 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
parent: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
0 siblings, 0 replies; 21+ messages in thread
From: Louis-David Mitterrand @ 2010-03-03 16:38 UTC (permalink / raw)
To: 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,
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2010-03-03 16:38 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-09-14 13:24 CHECK constraint on multiple tables Mario Splivalo <mario.splivalo@megafon.hr>
2009-09-14 13:27 ` Ries van Twisk <pg@rvt.dds.nl>
2009-09-14 13:58 ` Mario Splivalo <mario.splivalo@megafon.hr>
2009-09-14 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-09-14 14:20 ` Mario Splivalo <mario.splivalo@megafon.hr>
2009-09-14 17:00 ` Thomas Kellerer <spam_eater@gmx.net>
2009-09-16 03:34 ` Rob Sargent <robjsargent@gmail.com>
2010-03-03 15:02 check constraint on multiple tables? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-03 15:14 ` Re: check constraint on multiple tables? Richard Broersma <richard.broersma@gmail.com>
2010-03-03 15:19 ` Re: check constraint on multiple tables? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-03 15:29 ` Re: check constraint on multiple tables? Richard Broersma <richard.broersma@gmail.com>
2010-03-03 15:38 ` Re: check constraint on multiple tables? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-03 15:57 ` Re: check constraint on multiple tables? Richard Broersma <richard.broersma@gmail.com>
2010-03-03 16:05 ` Re: check constraint on multiple tables? Oliveiros <oliveiros.cristina@marktest.pt>
2010-03-03 16:17 ` Re: check constraint on multiple tables? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-03 16:29 ` Re: check constraint on multiple tables? Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-03-03 16:38 ` Re: check constraint on multiple tables? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-03 16:03 ` Re: check constraint on multiple tables? Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-03-03 16:06 ` Re: check constraint on multiple tables? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-03-03 16:13 ` Re: check constraint on multiple tables? Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-03-03 16:21 ` Re: check constraint on multiple tables? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
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