Re: arc relationship [was: db design question] - Mailing list pgsql-novice

On Tue, 2002-10-22 at 22:34, Jules Alberts wrote:
> 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.

Oh, I see.

Yes, the standard way to deal with this is a to 'normalise' the data
through intermediate tables.

In my past I have usually used an 'address_type' or 'contact_type' field
to additionally identify these relationships, but that is probably
irrelevant to this question.

Basic form is:

company ( company_id, company_data );
address ( address_id, address_data);
employee ( employee_id, employee_data );
company_address( company_id, address_id );
employee_address( employee_id, address_id );

However, if you can guarantee that company_id and employee_id are
non-colliding (e.g. you use the same sequence to generate them) then
company_address and employee_address can be combined into a single
table.

Alternatively, a defining attribute can be added to identify the source,
so you can have individual sequences within the source tables:

contact_address( contact_source, contact_id, address_id )

Where a 'contact_source' of 'E' might identify that contact_id is an
employee_id or 'C' might indicate a company_id and so on.


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

Yes.  I've heard them called other things too (polite ones, even :-).


> 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

Note that the 'contact_source' can be anything you want - it doesn't
have to be the full table name from the source table.  It is probably
better if it isn't, in fact, because then you are restricted to a single
reference field in your source table.

Consider a company record that required two signatories to be recorded,
each with their address.  This could potentially (easily) be handled as
two fields on the company record, but you would need your
'contact_source' linking to their address to indicate not just the
table, but the field as well.  Usually it is easier to do this
symbolically, than to use the full table.field sort of notation (which
_still_ might not support arrays, or whatever).

Cheers,
                    Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
           Survey for free with http://survey.net.nz/
---------------------------------------------------------------------