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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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