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

arc relationship [was: db design question]

From: "Jules Alberts" <jules(dot)alberts(at)arbodienst-limburg(dot)nl>
To: pgsql-novice(at)postgresql(dot)org
Subject: arc relationship [was: db design question]
Date: 2002-10-22 09:34:39
Message-ID: 200210220934.g9M9YOE2011885@artemis.cuci.nl (view raw or flat)
Thread:
Lists: pgsql-novice
On 22 Oct 2002 at 21:30, Andrew McMillan wrote:
> On Tue, 2002-10-22 at 20:14, Jules Alberts wrote:
> > On 21 Oct 2002 at 8:44, Josh Berkus wrote:
> > 
> > So let's say I don't use OID. Is a SEQUENCE bound to several tables the 
> > best solution? Any other suggestions?
> 
> The sequence doesn't have to be bound to several tables - just your
> address table:
> 
> Josh originally said:
> > 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))

Hello Andrew,

That wouldn't solve my problem, because I want to be able to store more 
addresses per company / client / consultant. Also, I have other tables 
(action and blobs) that do the same.

I had a conversation with an Oracle DBA this morning. After explaining, 
which took some time :-), he told me that what I wanted is in Oracle 
called an "arc" relationship. Knowing this word, Google was a lot 
friendlier, I found a good explanation here:
 
http://www.stormloader.com/yonghuang/computer/SemanticallyDependentAttri
butes.txt

Apparently there are several solutions:

1. create an intermediate table for every table you want address / 
action / blobs to be related to. This would create a big overhead, I 
don't like that.

2. in address / action / blobs create a column for each table it is 
related to. Enforce that per row only one of these columns is filled. 
The value would be the primary key of the related table. The other 
columns would be NULL, which could cause problems.

3. in address / action / blobs create a column that contains the unique 
identifier of the row in the related table. This is the OID / sequence 
approach I was thinking about.

4. same as 3., but in address / action / blobs also create a column 
that contains the name of the related table. This will makes a backward 
search (which is the company / client / consultant this address belongs 
to) easier.

I think number 4 is the best solution. Create a sequence from which all 
tables get their primary key and include an integer row in address etc 
that points to the primary key of the related table and store the name 
of that table. Things to keep an eye on:

- don't change table names without changing the values in address etc
- set a huge maximum value for the global sequence

Thanks for your reactions everyone! If there is more feedback / 
remarks, please let me know. TIA!

In response to

Responses

pgsql-novice by date

Next:From: Erik PriceDate: 2002-10-22 09:51:42
Subject: Re: Big Picture
Previous:From: Andrew McMillanDate: 2002-10-22 08:30:16
Subject: Re: db design question

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