Re: many to one of many modeling question

On Mon, Jan 07, 2008 at 02:32:26PM -0500, Kevin Hunter wrote:
> Something along the lines of
>
> CREATE TABLE o_1 ( id SERIAL ... );
> CREATE TABLE o_2 ( id SERIAL ... );
> CREATE TABLE o_3 ( id SERIAL ... );
> CREATE TABLE comments (
> id SERIAL,
> obj_id INTEGER ...
> FOREIGN KEY (obj_id) REFERENCES ONE OF o_1(id), o_2(id), o_3(id)
> );
>
> This obviously won't syntactically work, but you perhaps get the drift ...

I've done this sort of thing before:

CREATE TABLE comments (
id SERIAL PRIMARY KEY,
otype INTEGER NOT NULL CHECK (otype IN (1,2,3)),
o1id INTEGER REFERENCES o_1, CHECK ((otype = 1) = (o1id IS NOT NULL)),
o2id INTEGER REFERENCES o_2, CHECK ((otype = 2) = (o2id IS NOT NULL)),
o3id INTEGER REFERENCES o_3, CHECK ((otype = 3) = (o3id IS NOT NULL))
);

that way everything is contained in one table. It's a bit fiddly to
use though if you want to do things like get all the comments, and some
identifier out from each object:

SELECT c.id, c.description, o1.identifier,o2.barcode,o3.prodcode
FROM comments c
LEFT JOIN o_1 o1 ON c.o1id = o1.id
LEFT JOIN o_2 o2 ON c.o2id = o2.id
LEFT JOIN o_3 o3 ON c.o3id = o3.id;

Not too bad though.

Sam