Reference key from Either of Two Different Tables [closed]

Closed. This question needs to be more focused. It is not currently accepting answers.

---

开发者_StackOverflow

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 4 years ago.

Improve this 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?

---

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

---

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

---

Doing this in a single query should not be the main goal, you should consider a right design and an appropriate normalization level of tables.

Basically - query should fit design but not opposite.

Try in this way, create following tables:

-- all common fields for any user (user can be in one group in one time)
USERS(Id, GroupId, Name, Phone, Address)

--User groups like Employee, BusinessMan
USER_GROUPS(Id, Name)

-- The Permanent employees accounting information
ACCOUNTING_PERMANENT(Id, UserId, Designation, Salary)

-- The Business man accounting information
-- Perhaps you can use PE ('private employment') instead 'Business Man'
ACCOUNTING_BUSINESS(Id, UserId, Name, TurnOwer, BusinessType)

If a business can be owned by many PEs (business mans) create one more table and remove UserId from ACCOUNTING_BUSINESS table

-- Relation between USERS and ACCOUNTING_BUSINESS tables
USERS_BUSINESS(Id, UserId, BusinessId)

---

Your design is fine.

If there were a lot of common elements between employee and business_man, then you might want to introduce a supertype/subtype model as hinted at by sllev. If there isn't a lot of commonality, then the supertype/subtype model may be good for a conceptual model but impractical for a physical model.

What you are doing in your design is implicitly using a kind of supertype/subtype in which there are no common attributes between employees and business men, except what you are calling code_number and an integrated identifier (Clients.id). Your partitioning attribute is Clients.type.

To simplify retrieval of information in a single query, define a view that provides a UNION of the join between Employees and Clients with the join between Business_Men and Clients.

This will give you what looks like a single, flattened table that contains both kinds of clients at the same time.