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: web-1787246@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

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-2014 The PostgreSQL Global Development Group