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

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 10:12:25
Message-ID: 200210161014.g9GAEQTW007033@artemis.cuci.nl (view raw or flat)
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.  You can
> even automate the creation and relationship of addresses to companies,
> employees, etc. through VIEWS and RULES.

Thanks, great advice!

> 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.

OK, I will have a look.

> 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.

I'll drop it in this case, your approach "feels" a lot safer. However, 
I plan to store BLOBs in my db, and is this case I'm afraid I will 
_have_ to use OIDs. The idea is to be able to "attach" a blob to _any_ 
row in the db. An example of how I planned to use it (this works BTW)

-- import a BLOB
insert into blobs (ref_oid, blob_oid, blob_name, description) values (
  (select oid from employee where name='Jules'), 
  lo_import('/usr/share/pixmaps/gimp.png'),
  '/usr/share/pixmaps/gimp.png',
  'test: imported picture');

-- retrieve the BLOB
select lo_export((select blob_oid from blobs where ref_oid=
  (select oid from employee where name='Jules')),
  '/tmp/gimp.png');

One pitfall I was already warned for: allways use the datatype OID, or 
get in trouble when dumping / restoring, as the actual values of the 
OIDs change.

In response to

Responses

pgsql-novice by date

Next:From: Jules AlbertsDate: 2002-10-16 12:53:04
Subject: Re: db design question
Previous:From: Oliver ElphickDate: 2002-10-16 07:14:45
Subject: Re: displaying records from X to Y

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