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@myemma.com
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
