Re: db design question

From: "Jules Alberts" <jules(dot)alberts(at)arbodienst-limburg(dot)nl>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: db design question
Date: 2002-10-16 12:53:04
Message-ID: 200210161254.g9GCsZTY009794@artemis.cuci.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On 15 Oct 2002 at 9:38, Josh Berkus wrote:
> 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.
<snip>

I just thought of something... If I wanted the possibility to have more
than one address per employee / company / consultant the OID approach
would be better.

A way (other than using OIDs) to solve this "(several tables):N" issue
(if you know what I mean) might be using an array for the address
references:

company(name varchar(50), address_id INT[])

but somehow I don't like the idea of an array datatype -a table within
a table- in the relational model.

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Mattia Boccia 2002-10-16 13:46:25 Re: information
Previous Message Jules Alberts 2002-10-16 10:12:25 Re: db design question