Re: db design question

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.