Re: Conditional Relationships?

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