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
