DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type:Message-ID;
	b=R7Ew2YwGSI2tvJikRTLy9He2uMs9bDCZKrSjvEATqNHkCyuYsOp082nft89rd9CWo2t848+ffHfPB/Hbjz1FPzwYwSBWVEtiJDrUpggPJWNGXNDr0R1y10ghQBFmYS6FUPdCzd1svvs5Xz/5RCy75H1BWTKJ0MMO9jvRkmG9ETQ=;
Date: Wed, 21 Nov 2007 23:44:47 -0800 (PST)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Bad Schema Design or Useful Trick?
To: SQL Postgresql List <pgsql-sql@postgresql.org>
Cc: General PostgreSQL List <pgsql-general@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Message-ID: <82640.215.qm@web31815.mail.mud.yahoo.com>

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.