[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] Bad Schema Design or Useful Trick?
From:       Richard Huxton <dev () archonet ! com>
Date:       2007-11-22 13:46:03
Message-ID: 4745881B.6010904 () archonet ! com
[Download RAW message or body]

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
[prev in list] [next in list] [prev in thread] [next in thread] 

Configure | About | News | Add a list | Sponsored by KoreLogic