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
