Reference key from Either of Two Different Tables [closed]

Question

I have the following two tables:

employees(id, name, address, designation, salary, phone, email...)

business_men(id, business_type, business_name, turn_over, phone, email,...)

Now I have another table clients. My client can be of type employee or business_man. So I have the client table as follows:

Clients(id, code_number, type, reference_id)

Type field can store 'employee' or 'business_man' and the reference_id is the id of the table mentioned in Type field. The Id of the client is used throughout the application at many places.

My Questions are:

1. Am I doing it right?
2. Is there a better / efficient way to do it?
3. How can I get the full details of a client (given the client ID) using a single SQL query?

Answer 1

Note that ClientID is just a role-name for PersonID (ClientID = PersonID).

With this you can now crerate several views to make life easier, here are some viewes that you could use:

• Employees
• BusinessPeople
• People
• Clients
• EmployeeClients
• BusinessClients

Answer 2

A slightly different approach we used was to create a abstract entity table. It's purpose was to provide a unique sequence number to all the concrete entities. A simplified example follows

--CREATE SCHEMA user893847

CREATE TABLE user893847.BASE_ENTITY
(
    entity_id int identity(1,1) NOT NULL PRIMARY KEY
)

CREATE TABLE user893847.EMPLOYEE
(
    entity_id int NOT NULL PRIMARY KEY
,   name_first varchar(30) NOT NULL
,   name_last varchar(30) NOT NULL
)

CREATE TABLE user893847.BUSINESS_PERSON
(
    entity_id int NOT NULL PRIMARY KEY
,   company_name varchar(30) NOT NULL
)

CREATE TABLE user893847.ADDRESS
(
    entity_id int NOT NULL
,   address_line1 varchar(70) NOT NULL
)

Our insert methods would make inserts into the BASE_ENTITY table and capture the resulting id value. The concrete tables (employee, business_person) would store the resulting id as their PK. One of the main reasons for this was our business, marketing, could have us moving entity tables as we learn more about them or reclassify an individual. We found it simplified the logic if entity 478 is the "same" throughout the domain. Rather than having to do queries based on type in your design, because a number is redefined in each table, you query simply joins to the table and if rows come back, it is that type.

-- your query
SELECT 
    C.*
,   E.*
    -- build out a null set of colums for business men
,   NULL AS id
,   NULL AS business_type
FROM
    Clients C 
    INNER JOIN 
        Employees E 
        ON E.id = C.reference_id 
WHERE 
    C.type = 'employees'
UNION ALL

SELECT 
    C.*
    -- repeat the build out for faking the employee columns
,   NULL AS id
,   NULL AS name
,   ...
,   BM.* 
FROM 
    Clients C 
    INNER JOIN 
        business_men BM 
        ON BM.id = C.reference_id 
WHERE 
    C.type = 'employees'



-- my aproach
SELECT 
    C.*
,   E.*
    -- build out a null set of colums for business men
,   NULL AS id
,   NULL AS business_type
,   ...
FROM
    Clients C 
    INNER JOIN 
        Employees E 
        ON E.id = C.reference_id 
UNION ALL

SELECT 
    C.*
    -- repeat the build out for faking the employee columns
,   NULL AS id
,   NULL AS name
,   ...
,   BM.* 
FROM 
    Clients C 
    INNER JOIN 
        business_men BM 
        ON BM.id = C.reference_id 

Let me know if you have questions about the design