sql schema advice sought

From: Jonathan Vanasco <postgres(at)2xlp(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: sql schema advice sought
Date: 2007-04-03 07:52:32
Message-ID: 3082263D-3494-4299-8C5B-2DFB7D9F810B@2xlp.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-04-03 08:00:17 Re: Using C# to create stored procedures
Previous Message Magnus Hagander 2007-04-03 07:24:29 Re: Using C# to create stored procedures