Re: db design question - Mailing list pgsql-novice

On 20 Oct 2002 at 19:16, Vijay Deval wrote:
> Hi Jules

Hello Vijay,

> What will happen to oid if you dump the table, upgrade PostgreSQL and
> recreate tables?

A dump / restore (you don't even need an upgrade) will change the value
of the OIDs. However, it will keep the relations working. Like this:

create table person(name varchar);
insert into person values ('Joe');
select oid from person where name = 'Joe'; -- e.g. 123456
create table address (ref_oid oid, street varchar);
insert into address values (123456, 'Penny Lane');

dump + restore

select oid from person where name = 'Joe'; -- new value! 888888
select stree from address where ref_oid = 888888 -- 'Penny Lane'

I tested this and it works. You just have to avoid hardcoded OID's in
your app, allways works with a subselect. Still, there is a translation
going on in the dump / restore mechanism. That's one step extra that
could cause a problem.

> In case a company has more than one consultent, company is going to
> feature in more than one tuples with different oid's.
>
> It might be a better idea to have two tables. One table assigns serial
> id number to company. Other table could have this serial id as one
> attribute , along with other things like consultant name etc. This
> should avert problem of repeating data.

I already do that.

> It is quite possible that one consultant gives consultations to more
> than one companies. In that case the data needs to be stored in three
> tables.

That too.

The actual problem was that I have several tables (address, BLOBs,
actions etc.) that are possibly related to _any_ table in my db. A
solution could be to create a referring column from every table to an
INT primary key in address, action etc., but that would limit the link
to one at most. For "address" this is probably OK, but for "actions" an
"BLOBs" surely not. I guess I could work with intermediate tables
(between "any table" and address, BLOB's and actions), but that would
create an overhead of factor 2. Quite a bit IMO.

That's why I'm looking at the possibilities of using OID for this.
Advantage:
- OID is global (througout the db) unique, even if I don't care from
  which table a column comes, I will find it.

Disadvantages:
- more coding in select stataments. Each select that involves one of
  the address / BLOBs / actions tables will be about twice the size
- the changing of OID value with dump and restore. This makes me a bit
  nervous, allthough the longer select statements should cover this

There are many people saying "don't do it". :-) OIDs are considered an
internal system mechanism that shouldn't be used in everyday life. If
this is the common view on OIDs the developers may well change their
behaviour, or replace them with some other mechanism ("why not?
nobody's using them anyway").

A workaround for the address and actions tables may be using a global
sequence and use nextval() primary keys for every table. Someone
recommended a book (Pascal: practical issues in db management). I've
ordered it and will read it, hope it will help me make up my mind.