Re: many to one of many modeling question

On Mon, Jan 07, 2008 at 12:30:50PM -0500, Kevin Hunter wrote:
> Hi List,
>
> I have multiple objects to which I'd like to associate comments. I'd like
> this to be a many to one relationship, so that each object can have many
> different comments. The issue is how to have one comment table. One method
> that has been proposed is to have a third table which stores to what object
> type a comment belongs, but I don't like this because the foreign key
> relationships then wouldn't be maintained by the database. The only way
> that I'm able to think of at the moment is multiple columns.
>
> Is there a clever/clean way of having the comments foreign key into the
> multiple tables?

While I'm not recommending that you do this, here's one way:

CREATE TABLE foo_1 (
foo_1_id SERIAL PRIMARY KEY,
foo_1_text TEXT NOT NULL,
-- ...
);

CREATE TABLE foo_2 (
foo_2_id SERIAL PRIMARY KEY,
foo_2_text TEXT NOT NULL,
-- ...
);

CREATE TABLE foo_3 (
foo_3_id SERIAL PRIMARY KEY,
foo_3_text TEXT NOT NULL,
-- ...
);

CREATE TABLE foo_4 (
foo_4_id SERIAL PRIMARY KEY,
foo_4_text TEXT NOT NULL,
-- ...
);

CREATE TABLE foo_5 (
foo_5_id SERIAL PRIMARY KEY,
foo_5_text TEXT NOT NULL,
-- ...
);

CREATE TABLE refs_all_foo AS (
foo_1_id INTEGER REFERENCES foo_1,
foo_2_id INTEGER REFERENCES foo_2,
foo_3_id INTEGER REFERENCES foo_3,
foo_4_id INTEGER REFERENCES foo_4,
foo_5_id INTEGER REFERENCES foo_5,
CHECK (
CASE WHEN foo_1_id IS NULL THEN 0 ELSE 1 END +
CASE WHEN foo_2_id IS NULL THEN 0 ELSE 1 END +
CASE WHEN foo_3_id IS NULL THEN 0 ELSE 1 END +
CASE WHEN foo_4_id IS NULL THEN 0 ELSE 1 END +
CASE WHEN foo_5_id IS NULL THEN 0 ELSE 1 END = 1
)
);

CREATE VIEW polymorphic_foo AS
SELECT
CASE
WHEN foo_1_id NOT NULL THEN 'foo_1'
WHEN foo_2_id NOT NULL THEN 'foo_2'
WHEN foo_3_id NOT NULL THEN 'foo_3'
WHEN foo_4_id NOT NULL THEN 'foo_4'
WHEN foo_5_id NOT NULL THEN 'foo_5'
END AS "which_foo",
COALESCE(
foo_1_id,
foo_2_id,
foo_3_id,
foo_4_id,
foo_5_id
) AS "id"
FROM
refs_all_foo;

You can then make this VIEW writeable by the usual methods.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate