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

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.
************************************************** *********************

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

On Apr 3, 2007, at 9:56 AM, Jaime Silvela wrote:

> 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

thats perfect, and simple.

the unified table is too dirty i've done stuff like that in the
past, and was always upset with it.




// Jonathan Vanasco

| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - -
| FindMeOn.com - The cure for Multiple Web Personality Disorder
| Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - -
| RoadSound.com - Tools For Bands, Stuff For Fans
| Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - -



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