From: Richard Huxton <dev@archonet.com>
To: Richard Broersma Jr <rabroersma@yahoo.com>
Cc: SQL Postgresql List <pgsql-sql@postgresql.org>
Cc: General PostgreSQL List <pgsql-general@postgresql.org>
Subject: Re: [SQL] Bad Schema Design or Useful Trick?
Date: Thu, 22 Nov 2007 13:46:03 +0000
Message-ID: <4745881B.6010904@archonet.com> (raw)
In-Reply-To: <82640.215.qm@web31815.mail.mud.yahoo.com>
References: <82640.215.qm@web31815.mail.mud.yahoo.com>
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
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: dev@archonet.com, rabroersma@yahoo.com, pgsql-general@postgresql.org
Subject: Re: [SQL] Bad Schema Design or Useful Trick?
In-Reply-To: <4745881B.6010904@archonet.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