Is there a way that an attribute can reference another attribute from either of 2 other tables?

Question

Sorry for that title, english isn't my first language. I'm wondering if there is a way to structure my tables/cardinality so that when a certain job is performed, the job_location (JOB table) will either be a home_address from the HOME Table or a work_address from the WORK table and not just a VARCHAR like it is at the moment?

I'm not sure if that makes sense, I'm new and I'm trying to see if I can do it without using things like triggers.

For example consider the picture above, where job_location can be either a home_address or a work_address, not both and not none.

Answer 1

As mentioned in the comments, you should have one address table. Here's why.

Many data modelers, when going through the original design, will take a human language description of the real-world objects being modeled and make lists of the nouns and adjectives. The nouns are generally the entities and the adjectives are generally the attributes. So when you come across "home address" and "work address", you should have placed "address" in the noun list and "home" and "work" in the adjective list. This means you should have a table called Addresses with one field called something like "AddressType" which would designate the address as home or work.

Because the same user (or employee or whatever) can have one home address and/or one work address, the address table would look like this:

create table Addresses(
    EmpID    int not null references Employees,
    AddrType char( 1 ) check( AddrType in( 'H', 'W' ),
    ...,     -- other address data
    constraint PK_Addresses primary key( EmpID, AddrType )
);

So the Jobs table would look like this:

create table Jobs(
    ID       int identity primary key,
    LocOwner int not null,
    LocType  char( 1 ),
    ...,     -- other job data
    constraint FK_Jobs_Location foreign key( LocOwner, LocType )
        references Addresses( EmpID, AddrType )
);

I've shown the PK of the Addresses table as the combination of employee id and type (H or W). This allows the same employee to have two addresses, one of each kind. This simplifies greatly the search for all addresses associated with an employee. Notice that this is a closed solution: the address data in the job tuple must refer to an existing address in the Addresses table with the correct type designation.

However, you may be stuck with the design you have. That's unfortunate, but there is still a solution, though not as simple and straightforward as having it well designed in the first place. Here is how it would look and how to make it.

First, create an address type table like the first two columns of the address table above. However, since my design incorporated the employee id but yours has a surrogate address key, let's stick with your design.

create table AddresseTypes(
    Addr_ID   int not null,
    Addr_Type char( 1 ) check( Addr_Type in( 'H', 'W' ),
    constraint PK_AddresseTypes primary key( Addr_ID, Addr_Type )
};

Second, each address table must have a separate table with the type as shown above.

create table HomeAddresses(
    Home_ID    int not null references Home( Home_ID ),
    Home_Type  char( 1 ) check( HomeType = 'H' )
);
create table WorkAddresses(
    Work_ID    int not null references Work( Work_ID ),
    Work_Type  char( 1 ) check( WorkType = 'W' )
);

These tables contain either all the addresses listed in their respective address table or just the ones associated with a job. Copy the entries from these table into the AddresseTypes table. Then:

alter table HomeAddresses add constraint FK_HomeAddressType
    foreign key( Home_ID, Home_Type )
    references AddressTypes( Addr_ID, AddrType );
alter table WorkAddresses add constraint FK_WorkAddressType
    foreign key( WorkID, WorkType )
    references AddressTypes( Addr_ID, AddrType );
alter table Jobs add constraint FK_JobAddress
    foreign key( Loc_ID, Loc_Type )
    references AddressTypes( Addr_ID, AddrType );

No entry can exist in HomeAddresses or WorkAddresses that do not exist in Home or Work respectively and in AddressTypes. No Job entry can refer to an address that is not listed in AddressTypes.

Unfortunately, this is not a closed solution: AddressTypes can contain spurious entries not found in either HomeAddress or WorkAddresses. This makes for extra maintenance effort, but it wouldn't be difficult.

To query a job and get the address from whichever table contains it, the query has to outer join with both Home and Work tables.

select  j.*, 
        case t.Addr_Type  -- repeated for each address field
            when 'W' then w.Street
            else h.Street end as Street, 
        case t.Addr_Type
            when 'W' then w.City
            else h.City end as City,
        case <etc.>
from    Jobs j
join    AddressTypes t
    on  t.Addr_ID = j.Loc_ID
    and t.Addr_Type = j.Loc_Type
left join HomeAddresses ha
    on  ha.Home_ID = t.Addr_ID
    and ha.Home_Type = t.Addr_Type
left join WorkAddresses wa
    on  wa.Work_ID = t.Addr_ID
    and wa.Work_Type = t.Addr_Type
left join Home h
    on  h.Home_ID    = t.Addr_ID
left join Work w
    on  w.Work_ID    = t.Addr_ID;

If the address data shows up as a series of NULLs, it means the entry in AddressTypes is spurious. Easily fixed. As the same address id can exist in both Home and Work, the case statements select the correct source.

Obviously, you are much better off if you are free to change the design.