data modeling question

From: Brandon Metcalf <brandon(at)geronimoalloys(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: data modeling question
Date: 2009-06-26 19:51:49
Message-ID: Pine.LNX.4.58L.0906261427380.6649@cedar.geronimoalloys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I asked a question similar to this a couple of weeks ago, but the
requirement has changed a bit and I want to be sure I'm designing my
tables correctly.

I have the following table:

CREATE TABLE workorder (
number VARCHAR(8),
quantity INTEGER,
generic BOOLEAN,

PRIMARY KEY (number)
);

If generic is true, number will need to be associated with at least
one other number in the same table. I need to ensure the integrity of
this association. So, I'm thinking a second table:

CREATE TABLE generic (
gnumber VARCHAR(8),
number VARCHAR(8),

PRIMARY KEY (gnumber, number),

FOREIGN KEY (gnumber)
REFERENCES workorder(number)
ON DELETE RESTRICT
ON UPDATE CASCADE,

FOREIGN KEY (number)
REFERENCES workorder(number)
ON DELETE RESTRICT
ON UPDATE CASCADE
);

Any better way of doing this?

--
Brandon

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dimitri Fontaine 2009-06-26 20:10:09 Re: masking the code
Previous Message Merlin Moncure 2009-06-26 19:48:46 Re: Terrible Write Performance of a Stored Procedure