arc relationship [was: db design question] - Mailing list 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!