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