I have a similar situation. Here's what I do.
I have a stand-alone comment table:
Comments
id
timestamp
text
Then I have individual product tables to tie a table to a comment:
Table_A_Comment
id
id_ref_a references tableA
id_comment references Comments
The Table_*_Comment tables can be unified into one, of course:
Table_Comment
id
id_comment references Comments
id_ref_a references tableA
id_ref_b references tableB
id_ref_c references tableC
In my view, the advantage is that you keep concepts separate: the
structure of comments does not depend on the tables it comments.
Also, the product table/s give you more flexibility if, say, you decide
a comment can apply to more than one object.
Jonathan Vanasco wrote:
I'm redoing a sql schema , and looking for some input
First I had 2 tables :
Table_A
id
name
a
b
c
Table_B
id
name
x
y
z
as the project grew, so did functionality.
Table_A_Comments
id
id_refd references Table_A(id)
timestamp
text
Table_B_Comments
id
id_refd references Table_B(id)
timestamp
text
well, it just grew again
Table_C
id
name
m
n
o
Table_C_Comments
id
id_refd references Table_B(id)
timestamp
text
Now:
Table_A , Table_B , and Table_C are all quite different.
But:
Table_A_Comments , Table_B_Comments , Table_C_Comments are
essentially the same -- except that they fkey on different tables.
I could keep 3 sep. tables for comments, but I'd really like to
consolidate them in the db -- it'll be easier to reference the data in
the webapps that query it .
My problem is that I can't figure out a way to do this cleanly , while
retain integrity.
When dealing with this In the past, I used a GUID table
Table_ABC_guid
guid , child_type [ A , B, C ] , child_id
and then add a guid column onto each table that FKEYS it.
On instantiation of a new row in A, B, C I would create a GUID
record and then update the row with it. general tables would ref the
guid, not the real table.
I can't help but feel thats still a dirty hack, and there's a better
way. That didn't solve my integrity problems, it just shifted them
into a more manageable place.
Anyone have a suggestion ?
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.
Bear Stearns does not provide tax, legal or accounting advice. You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of: (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************
