Jules,
> My idea for the new db was someting like this:
>
> company(name varchar(100))
> employee(code int)
> consultant(name varchar(50))
> address(ref_oid OID, street varchar(100), state varchar(100))
>
> In this way, I can store all the addresses together and find them
> with.
> SELECT * WHERE addres.ref_oid = company.oid;
That's a fine idea, except that you have the referential integrity
backward:
Company(name varchar(100), address_id INT)
employee(code int, address_id INT)
consultant(name varchar(50), address_id INT)
address(address_id INT PRIMARY KEY, street varchar(100), state
varchar(100))
While there are reasons to do the kind of multi-table join that you
propose, the standard relational model (above) works better. You can
even automate the creation and relationship of addresses to companies,
employees, etc. through VIEWS and RULES.
I heartily reccomend "Practical Issues in Database Management" to you.
Fabian Pascal, the author, treats extensively some of the pitfalls of
getting unneccessarily creative with the relational model.
BTW, don't use the OID. The OID, as of 7.2.0, is for *system purposes
only* and should not be used for queries, joins, indexes, or keys. If
you need a table-indepentant unique ID, use a sequence.
-Josh Berkus
