Re: Table design - postgresql solution

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(at)if-then-else(dot)pl