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-21 07:01:51
Message-ID: 200210210705.g9L74sE4027077@artemis.cuci.nl (view raw or flat)
Thread:
Lists: 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.

In response to

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-10-21 15:44:30
Subject: Re: db design question
Previous:From: Erik PriceDate: 2002-10-21 02:22:54
Subject: Re: Big Picture

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