Re: sql schema advice sought - Mailing list pgsql-general

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