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?
