pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBad Schema Design or Useful Trick?
2+ messages / 2 participants
[nested] [flat]
* Bad Schema Design or Useful Trick?
@ 2007-11-22 07:44 Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: Richard Broersma Jr @ 2007-11-22 07:44 UTC (permalink / raw)
To: pgsql-sql; +Cc: General PostgreSQL List <pgsql-general@postgresql.org>
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,
unit_cost numeric(7,2) NOT NULL DEFAULT 0
CHECK( unit_cost >= 0 ),
description text NOT NULL,
CONSTRAINT parts_primary_key
PRIMARY KEY ( part_nbr, part_type ),
CONSTRAINT only_defined_part_types
CHECK( part_type IN
( 'pump', 'bolt', 'nut')));
CREATE TABLE Pumps (
part_nbr varchar( 100 ) PRIMARY KEY,
part_type varchar( 20 ) NOT NULL
CHECK( part_type = 'pump' ),
volumn real NOT NULL CHECK( volumn > 0 ),
motorhp_size varchar( 4 ) NOT NULL REFERENCES
Motortypes( motorhp_size),
CONSTRAINT parts_foreign_key
FOREIGN KEY ( part_nbr, part_type )
REFERENCES Parts( part_nbr, part_type)
ON DELETE CASCADE
ON UPDATE CASCADE);
CREATE TABLE Hardware (
part_nbr varchar( 100 ) PRIMARY KEY,
part_type varchar( 20 ) NOT NULL
CHECK( part_type IN ( 'bolt', 'nut' ),
thread_size varchar( 4 ) NOT NULL REFERENCES
Threadtypes( Thread_size ),
grading varchar( 4 ) NOT NULL REFERENCES
Gradingtypes( grading ),
CONSTRAINT parts_foreign_key
FOREIGN KEY ( part_nbr, part_type )
REFERENCES Parts( part_nbr, part_type)
ON DELETE CASCADE
ON UPDATE CASCADE);
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] Bad Schema Design or Useful Trick?
@ 2007-11-22 13:46 Richard Huxton <dev@archonet.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2007-11-22 13:46 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql; General PostgreSQL List <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-11-22 13:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-22 07:44 Bad Schema Design or Useful Trick? Richard Broersma Jr <rabroersma@yahoo.com>
2007-11-22 13:46 ` Richard Huxton <dev@archonet.com>
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