Skip site navigation (1) Skip section navigation (2)

Re: db design question

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: jules(dot)alberts(at)arbodienst-limburg(dot)nl, pgsql-novice(at)postgresql(dot)org
Subject: Re: db design question
Date: 2002-10-15 16:38:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice

> 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

  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

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

In response to


pgsql-novice by date

Next:From: Thad HumphriesDate: 2002-10-15 16:44:14
Subject: ECPG fails to handle hex constants
Previous:From: Josh BerkusDate: 2002-10-15 16:19:10
Subject: Re: Big Picture

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group