Search Postgresql Archives

Re: many to one of many modeling question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux