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?

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

John Browne wrote:

tb_addresses_2_offices to something like tb_mgr_addr_off where it would
need
mgr_addr_off_id PRIMARY KEY
manager_id FOREIGN KEY REFERENCES tb_managers
address_id FOREIGN KEY REFERENCES tb_address
office_id FOREIGN KEY REFERENCES tb_office
address_type_id FOREIGN KEY REFERENCES tb_address_types

then an entry in tb_managers can have 0+ entries in tb_mgr_addr_off. If
you did it this way you could create a manager without either an address
or an office. You could also make the address table more generic by
using either the cdn or us version of state/province and zip/postalcode,
and introducing a country field. I don't know if this is the best
solution for your situation or not, but hopefully it will give you a
different perspective for finding a solution.

Ron


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

John Browne wrote:

address situations in
foreign countries, AFAIK. We use this on some of our databases, but we
don't have enough
foreign data yet to know if there are problems.

just recently on GENERAL
list and the consensus was to use a constraint rather than a foreign
key, eg:

CREATE TABLE tb_addresses_2_offices(
office_id INT FOREIGN KEY REFERENCES tb_offices(office_id),
address1_id INT REFERENCES tb_address1(address_id),
address2_id INT REFERENCES tb_address2(address_id),
CHECK((address1_id IS NULL AND address2_id IS NOT NULL) OR (address1_id IS NOT NULL AND address2_id IS NULL))
);

Is this what you're looking for?

Ron




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly