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
Import Notes
Reply to msg id not found: AANLkTi=KiHihBDXNUQXEYXYV-LQ0PLr6p=tX0df=Drx0@mail.gmail.com
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
historyTable_2
id_t2
name
type
sizeTable_3
id_t3
name
location
colorI 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.