Conditional Relationships?

From: John Browne <jkbrowne(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Conditional Relationships?
Date: 2004-10-07 05:05:12
Message-ID: ccc606104100622053874a213@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice

Ok, I'm designing a new database for work, and I have run across a
situation where a "conditional relationship" makes sense. Here is a
*simplified* example of what I'm talking about:

tb_address_data_us
address_id
addr1
addr2
city
state
zip_code

tb_address_data_ca
address_id
addr1
addr2
city
province
postal_code

tb_offices
office_id
manager_name
date_opened

tb_addresses_2_offices
office_id
country_id
address_data_id

As you can see, the "link" table is tb_addresses_2_offices. This
model makes it easy to add additional address templates in the future,
just by adding a single data table for the new country. But, it means
the address_data_id can point to an id in any of the address data
tables. I've done some reading, and some say it's fairly common to
see this type of model. Others say it breaks database normalization
rules.

My question is, how often do you guys see this in "real-world"
scenarios? Obviously, it makes things like foreign key constraints
difficult to use..

Thoughts?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pierre-Frédéric Caillaud 2004-10-07 08:45:57 Re: interfaces for python
Previous Message Oliver Jowett 2004-10-07 04:52:50 Re: Postgres 8.0 + JDBC

Browse pgsql-novice by date

  From Date Subject
Next Message Pradeepkumar, Pyatalo (IE10) 2004-10-07 05:26:00 Help with trigger
Previous Message Todd Kover 2004-10-06 21:54:48 splitting up a row in a table