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