Re: many to one of many modeling question

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?
>

If, by object, you mean that you have several tables, each row of which
should be associated with one or more comments, the best way would be to
create join tables for each of those tables:

CREATE TABLE object_1 (
id SERIAL ...
);
CREATE TABLE object_2 (
id SERIAL ...
);
CREATE TABLE object_3 (
id SERIAL ...
);
CREATE TABLE comments (
id SERIAL ...
);
CREATE TABLE comments_object_1 (
comments_id INT NOT NULL,
object_1_id INT NOT NULL,
CONSTRAINT FOREIGN KEY comment_id REFERENCES comments (id)
[ON DELETE ...],
CONSTRAINT FOREIGN KEY object_1_id REFERENCES object_1 (id)
[ON DELETE ...]
);
CREATE TABLE comments_object_2 (
comments_id INT NOT NULL,
object_2_id INT NOT NULL,
CONSTRAINT FOREIGN KEY comment_id REFERENCES comments (id)
[ON DELETE ...],
CONSTRAINT FOREIGN KEY object_2_id REFERENCES object_2 (id)
[ON DELETE ...]
);
CREATE TABLE comments_object_3 (
comments_id INT NOT NULL,
object_3_id INT NOT NULL,
CONSTRAINT FOREIGN KEY comment_id REFERENCES comments (id)
[ON DELETE ...],
CONSTRAINT FOREIGN KEY object_3_id REFERENCES object_3 (id)
[ON DELETE ...]
);

Out of curiosity, is this for a CakePHP app?

brian