Table design - postgresql solution

From: Miguel Vaz <pagongski(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Table design - postgresql solution
Date: 2010-12-04 00:20:43
Message-ID: AANLkTimVuH2cm_1C0Hk4hvVeVzx5aO3AjxZL0sxkc18b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have a bit of a DB design question, associated with postgresql in
particular, hopefully thinking it could solve my dilemma.

This is my setup of 3 tables:

Table_1
id_t1
name
date_of_discovery
history

Table_2
id_t2
name
type
size

Table_3
id_t3
name
location
color

I want a solution (table or groups of tables) where i can establish
relations between items of every table, for example:

row with id=2 from Table_1 is connected to row with id=23 from Table_3
id=9(from Table_2) is connected to id=83(from Table_1)
...
...etc

Stupid Solution 1:

I thought of creating a table:

"Relations"
id_1
type1
id_2
type2

where
"id_1" would be the id of the first item to make a connection
"type1" would indicate the table to where the item belongs to
"id_2" the otehr item to connect
"type2" the type of the second item

How would i be able to query such a beast? I know that programatically i can
make this work, but is there some easier solution?

Stupid Solution 2:

Crossed my mind to create a table:

"Things"
id_thing
name
type

And each other table would have a column named "id_thing" that would connect
to this one. This way, i would be able to list all items of every type from
a single table, and the relation table would just have id_1 and id_2,
pointing to this table. Depending on the column "type" on table "Things", i
could retrieve the rest of the field names of the appropriate item.

Bottom line: what i need is some way where i can connect items from Table_x
to Table_y.

Forgive me if i cant explain my problem in a more simple way. If i am being
stupid about this problem, its probably i am so darn close to it that i cant
see straight (or i am just plain stupid..).

Also i remember reading about table heritage (correct name?) and how several
tables can work together to complement each other. Does postgresql have some
inner working magic that can help on this case?

Thank you. Any help or finger pointing in the right direction is highly
appreciated.

MV

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-12-04 06:05:19 Re: encode(bytea_value, 'escape') in PostgreSQL 9.0
Previous Message Guillaume Lelarge 2010-12-03 23:32:05 Re: Looking for auto starting procedures