Re: Conditional Relationships?

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