Re: many to one of many modeling question

--- On Mon, 1/7/08, Kevin Hunter <hunteke(at)earlham(dot)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 table o.

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.