Re: many to one of many modeling question

--- On Mon, 1/7/08, Kevin Hunter <hunteke@earlham.edu> wrote:

> :-( Yeah this is one method.  I was hoping for something
> cleaner though.
>   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)
> );

you can get this to work if you create a table hiarachy instead where o_1, o_2, and o_3 area derived from a parent
tableo. 

CREATE TABLE o
( id SERIAL UNIQUE NOT NULL,
  obj_type VARCHAR CHECK( obj_type IN (1,2,3)) NOT NULL,
  PRIMARY KEY (id, obj_type)
 ... );

CREATE TABLE o_1
( id INTEGER UNIQUE NOT NULL,
  obj_type VARCHAR CHECK( obj_type = 1 ),
  PRIMARY KEY (id, obj_type),
  FOREIGN KEY (id, obj_type)
  REFERENCES o(id,obj_type)
  ON DELETE CASCADE ON UPDATE CASCADE,
 ... );
CREATE TABLE o_2 (
  id INTEGER UNIQUE NOT NULL,
  obj_type VARCHAR CHECK( obj_type = 2 ),
  PRIMARY KEY ( id, obj_type),
  FOREIGN KEY (id, obj_type)
  REFERENCES o(id,obj_type)
  ON DELETE CASCADE ON UPDATE CASCADE,
 ... );

CREATE TABLE o_3 (
  id INTEGER UNIQUE NOT NULL,
  obj_type VARCHAR CHECK( obj_type = 3 ),
  PRIMARY KEY ( id, obj_type),
  FOREIGN KEY (id, obj_type)
  REFERENCES o(id,obj_type)
  ON DELETE CASCADE ON UPDATE CASCADE,
 ... );

CREATE TABLE comments (
    id SERIAL,
    obj_id INTEGER ...
    FOREIGN KEY (obj_id)
    REFERENCES ONE OF o(id),
    ...
);

Regards,
Richard Broersma Jr.