From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
Cc: | SQL Postgresql List <pgsql-sql(at)postgresql(dot)org>, General PostgreSQL List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [SQL] Bad Schema Design or Useful Trick? |
Date: | 2007-11-22 13:46:03 |
Message-ID: | 4745881B.6010904@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
Richard Broersma Jr wrote:
> Below I've included sample table definitions for a vertically
> partitioned disjunctive table hierarchy. I wanted to point out the
> use of the composite primary key declaration that is applied to two
> columns that are clearly not a candidate key. However, using the
> badly defined primary key allows for referential integrity to nicely
> handle the de-normalization between the main table and sub tables
> that is inherent with this type of data model.
>
> Would using a primary key in this manner be a decision that I will
> regret in the long run? If so, can any explain why?
>
> The parent table is parts with the child table pumps and hardware.
>
> CREATE TABLE Parts ( part_nbr varchar( 100 ) UNIQUE NOT NULL,
> part_type varchar( 20 ) NOT NULL,
...
> PRIMARY KEY ( part_nbr, part_type ),
...
So - what you're saying is that because part_type depends on part_nbr it
shouldn't be part of the key, but because you want to search by
part-type in the referencing tables it makes life easier.
Will you regret this? Probably - I always seem to end regretting making
short-cuts, although in this case I can't see any direct harm that could
occur.
I'd probably make (part_nbr) the pkey and have a separate unique
constraint on (part_nbr,part_type) that I reference. That "feels" better
, although I'm not sure it actually gains you anything.
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Sorin N. Ciolofan | 2007-11-22 13:46:15 | Re: backup of postgres scheduled with cron |
Previous Message | luca.ciciriello | 2007-11-22 13:43:02 | Re: BEGIN strange behaviour |
From | Date | Subject | |
---|---|---|---|
Next Message | D'Arcy J.M. Cain | 2007-11-22 13:48:54 | Re: How to have a unique primary key on two tables |
Previous Message | Peter Eisentraut | 2007-11-22 13:19:23 | Re: Loading 8.2 data into 8.1 |