Thread: Table design - postgresql solution
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
Miguel, The idea of a table "relations" is good. It is a common solution and it is called "bridge", "cross-reference", "many-to-many resolver" or a "join table". Querying such a table would be easier if you had as many columns, as related tables. So for Table_1, Table_2 and Table_3 have a join table: Join_Table id_t1 - references to Table_1.id, NULL is allowed id_t2 - references to Table_2.id, NULL is allowed id_t3 - references to Table_3.id, NULL is allowed Think of records from Table_1, Table_2 and Table_3 as "ingredients" and of records from "Join_Table" as "recipe" or "mix". Querying "Join_Table" is very easy and you can easily retrieve the information on each "ingredient" using JOIN. You can even JOIN "Join_Table" with itself if you need. http://www.postgresql.org/docs/9.0/static/tutorial-fk.html http://www.postgresql.org/docs/9.0/static/tutorial-join.html To improve the integrity of your data, you may use "ON DELETE" and "ON UPDATE" instructions. Just take while and read this: http://www.postgresql.org/docs/9.0/static/sql-createtable.html Also - do not think in SQL. Try to organise your information in human way, as intuitively as you can. Once you have all the rules defined in that human way, move a step forward and put SQL into play. Use PostgreSQL to describe reality you understand, not to create reality you do not understand. That's why we have computers (mostly) :) Cheers, -- Michał Roszka mike@if-then-else.pl
Sat, 4 Dec 2010 13:24:27 +0000 Miguel Vaz <pagongski@gmail.com> > Your solution seems great and does the trick, but if most (and having > some items that relate to all 3 tables) of my relations are between > two tables, close to 1/3 of the id_tx fields would be null, correct? > I may be wrong in thinking like this, but always had this thing of > keeping tables very tight regarding null entries. Am i thinking > wrong? Or maybe worrying too much on less important things. :-) Well, test it. EXPLAIN is a cool thing. http://www.postgresql.org/docs/9.0/static/sql-explain.html http://explain.depesz.com/ http://explain.depesz.com/help/ You can do some optimisation and significantly improve performance of particular SELECT queries by creating proper indexes. I would not be too worried about the NULLs. I do not know, how much data and how large database we are talking about. Maybe you should consult someone more experienced. I only work with small databases: 2 GB of data, less than a million rows per table. Oh, and I am CC'ing the mailing list. ;) Cheers! -- Michał Roszka mike@if-then-else.pl
> 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) The common in many to many relations is to create for each two tables "joining" table tab1_tab2 with corresponding foreign keys to tab_1 and tab_2. If you want make general relation table, to keep all relations try with CHECK CONSTRAINT with own function inside.