Обсуждение: many to one of many modeling question
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? Thanks, Kevin
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
At 2:09p -0500 on 07 Jan 2008, brian wrote:
> Kevin Hunter wrote:
>> 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:
:-( 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)
);
This obviously won't syntactically work, but you perhaps get the drift ...
> Out of curiosity, is this for a CakePHP app?
Nope. This is for a customers private project. Sorry! :-)
Kevin
--- On Mon, 1/7/08, Kevin Hunter <hunteke@earlham.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
tableo.
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.
On Jan 7, 2008, at 2:22 PM, Richard Broersma Jr wrote: > CREATE TABLE comments ( > id SERIAL, > obj_id INTEGER ... > FOREIGN KEY (obj_id) > REFERENCES ONE OF o(id); Where are you guys getting this REFERENCES ONE OF stuff? Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
--- On Mon, 1/7/08, Erik Jones <erik@myemma.com> wrote: > From: Erik Jones <erik@myemma.com> > Subject: Re: [GENERAL] many to one of many modeling question > To: "Richard Broersma Jr" <rabroersma@yahoo.com> > Cc: "Postgres General List" <pgsql-general@postgresql.org>, "Kevin Hunter" <hunteke@earlham.edu>, "Caktus Team" <team@caktusgroup.com> > Date: Monday, January 7, 2008, 3:06 PM > On Jan 7, 2008, at 2:22 PM, Richard Broersma Jr wrote: > > > CREATE TABLE comments ( > > id SERIAL, > > obj_id INTEGER ... > > FOREIGN KEY (obj_id) > > REFERENCES ONE OF o(id); > > Where are you guys getting this REFERENCES ONE OF stuff? Oops, my mistake. The OP was using this fake syntax "REFERENCES ONE OF" simply to illustrate the functionality that wasdesired. I forgot to delete this bad syntax from my example. Thanks for the correction! Regards, Richard Broersma Jr.
On Jan 7, 2008, at 6:00 PM, Richard Broersma Jr wrote: > --- On Mon, 1/7/08, Erik Jones <erik@myemma.com> wrote: > >> From: Erik Jones <erik@myemma.com> >> Subject: Re: [GENERAL] many to one of many modeling question >> To: "Richard Broersma Jr" <rabroersma@yahoo.com> >> Cc: "Postgres General List" <pgsql-general@postgresql.org>, "Kevin >> Hunter" <hunteke@earlham.edu>, "Caktus Team" <team@caktusgroup.com> >> Date: Monday, January 7, 2008, 3:06 PM >> On Jan 7, 2008, at 2:22 PM, Richard Broersma Jr wrote: >> >>> CREATE TABLE comments ( >>> id SERIAL, >>> obj_id INTEGER ... >>> FOREIGN KEY (obj_id) >>> REFERENCES ONE OF o(id); >> >> Where are you guys getting this REFERENCES ONE OF stuff? > > Oops, my mistake. The OP was using this fake syntax "REFERENCES > ONE OF" simply to illustrate the functionality that was desired. I > forgot to delete this bad syntax from my example. > > Thanks for the correction! No worries. I was actually serious in that I thought I may have missed something! Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Mon, Jan 07, 2008 at 02:32:26PM -0500, Kevin Hunter wrote:
> 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)
> );
>
> This obviously won't syntactically work, but you perhaps get the drift ...
I've done this sort of thing before:
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
otype INTEGER NOT NULL CHECK (otype IN (1,2,3)),
o1id INTEGER REFERENCES o_1, CHECK ((otype = 1) = (o1id IS NOT NULL)),
o2id INTEGER REFERENCES o_2, CHECK ((otype = 2) = (o2id IS NOT NULL)),
o3id INTEGER REFERENCES o_3, CHECK ((otype = 3) = (o3id IS NOT NULL))
);
that way everything is contained in one table. It's a bit fiddly to
use though if you want to do things like get all the comments, and some
identifier out from each object:
SELECT c.id, c.description, o1.identifier,o2.barcode,o3.prodcode
FROM comments c
LEFT JOIN o_1 o1 ON c.o1id = o1.id
LEFT JOIN o_2 o2 ON c.o2id = o2.id
LEFT JOIN o_3 o3 ON c.o3id = o3.id;
Not too bad though.
Sam
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
--- On Fri, 1/11/08, David Fetter <david@fetter.org> wrote: > > 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. Actually, I think if you notice the example I posted previously, I made both the Unique ID column and object type columna composite primary key. The other tables referenced this composite primary key as foreign keys using referential integrity ON UPDATE CASCADE. Thismeans that they database ~would~ maintain/enforce the object-type portion of the foreign key automatically for you. Regards, Richard Broersma Jr.