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