Re: disjoint union types

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