Thread: Conditional Relationships?

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?

John Browne wrote:

>Yes, there will be different address types.  Sorry, I didn't represent
>the address type in the example.  Sorry about that.   The address to
>office relationship will need to be a many to many relationship,
>because of the different address types and the need to possibly share
>a ship-to address between multiple offices.  A manager may use his
>home address for a ship-to address, and share that between three
>different offices.
>
>tb_address_types
>address_type_id
>description         (ie, Office Location, Ship-to, Mail-to, etc)
>
>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_type_id
>address_data_id
>
>
>
One solution would be to create a tb_managers and change
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

John Browne wrote:

>Thanks for the reply.  That solution would work, except we are
>anticipating adding additional countries into the application, and
>their address field requirements would not match up with the US or CAN
>layouts.  This is why I was thinking it would be good to have each one
>in a seperate table.
>
If you added an addr3 field in tb_address it would probably cover most
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.

>I just am curious to know if it's bad database
>design for a single column to reference a foreign key in multiple
>different tables.
>
>
>
I assume you're referring to your original idea. There was talk of this
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