On Oct 9, 2007, at 9:38 AM, Sam Mason wrote:
Hi,
I was after opinions as to the best way to lay tables out to get the
effect of a "disjoint union" type (also known as a "tagged union").
When I have to do this at the moment, I'm creating a structure like:
CREATE TABLE circle ( id SERIAL PRIMARY KEY, radius REAL NOT
NULL );
CREATE TABLE square ( id SERIAL PRIMARY KEY, sidelen REAL NOT
NULL );
CREATE TABLE shapes (
id SERIAL PRIMARY KEY,
tag INTEGER NOT NULL,
circleid INTEGER REFERENCES circle
CHECK ((tag = 1) = (circleid IS NOT NULL)),
squareid INTEGER REFERENCES square
CHECK ((tag = 2) = (squareid IS NOT NULL))
);
I can then put data into this by doing:
BEGIN;
INSERT INTO circle (radius) VALUES (1);
INSERT INTO shapes (tag,circleid) VALUES (1,currval
('circle_id_seq'));
COMMIT;
This works, but it's sometimes a bit of a headache turning things
around
so they fit this structure. Are there standard solutions to this that
work better?
You could use after triggers on your circle and shape tables to
automatically make the insert into shapes for you.
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
