From: Richard Broersma Jr <rabroersma@yahoo.com>
To: SQL Postgresql List <pgsql-sql@postgresql.org>
Cc: General PostgreSQL List <pgsql-general@postgresql.org>
Subject: Bad Schema Design or Useful Trick?
Date: Wed, 21 Nov 2007 23:44:47 -0800 (PST)
Message-ID: <82640.215.qm@web31815.mail.mud.yahoo.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com, pgsql-general@postgresql.org
Subject: Re: Bad Schema Design or Useful Trick?
In-Reply-To: <82640.215.qm@web31815.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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