[Pdx-pm] (OT) Database design guru

[Michael G Schwern]

On Thu, Jan 23, 2003 at 04:47:24PM -0800, Curtis Poe wrote:
> I'm going nuts with a problem at work regarding a database normalization problem.  I've been searching high and low for an answer, but not luck so far.  On the off chance that anyone wants a conundrum to chew on:
> 
> http://www.perlmonks.org/index.pl?node_id=108949&user=Ovid

Seperate out the state and country fields into a more abstract "address"
table.  Technically this is denormalized, but its a nice logical
breakdown and handily solves your problem.

CREATE TABLE addresses (
    id		INTEGER		NOT NULL PRIMARY KEY,
    state	INTEGER		REFERENCES states,
    country     INTEGER		REFERENCES countries
);

CREATE TABLE customers (
    customer_id serial       NOT NULL PRIMARY KEY,
    first_name  VARCHAR(50)  NOT NULL,
    last_name   VARCHAR(100) NOT NULL,
    address	INTEGER      REFERENCES addresses
);

-- 

Michael G. Schwern   <schwern at pobox.com>    http://www.pobox.com/~schwern/
Perl Quality Assurance      <perl-qa at perl.org>         Kwalitee Is Job One