Reference key from two different tables

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 clients table. My client can be of type employee or business_man . Therefore, I have a customer table as follows:

 Clients(id, code_number, type, reference_id) 

The type field can store "employee" or "business_man", and reference_id is the identifier of the table specified in the "Type" field. The client ID is used throughout the application in many places.

My questions:

• Am I doing it right?
• Is there a better / efficient way to do this?
• How can I get full information about a client (taking into account the client identifier) ​​using a single SQL query?

---

Rauf Atar
Yarikul

Answer 1

A slightly different approach that we used was to create an abstract entity table. The goal was to provide a unique serial number for all specific objects. A simplified example is provided.

 --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 insertion methods are inserted into the BASE_ENTITY table and capture the received identifier value. Specific tables (employee, business_person) will store the received identifier as their PC. One of the main reasons for this is our business, marketing, that can force us to move entity tables as we learn more about them or reclassify people. We found that it simplifies the logic if object 478 is "the same" in the entire domain. Instead of making queries based on the type in your design, since the number is overridden in each table, the query simply joins the table, and if rows are returned, it's 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 any design questions.

Answer 2

Doing this in one query should not be the main goal; you should consider the correct design and the appropriate level of table normalization.

In principle, the request should be consistent with the design, but not vice versa.

Try to create the following tables this way:

 -- 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 belong to many PEs (businessmen), create another table and remove UserId from the ACCOUNTING_BUSINESS table

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

Answer 3

Note that ClientID is simply the role name for PersonID (ClientID = PersonID).

With this, you can now shorten several species to make life easier, here are some types that you could use:

• Employees
• ENTREPRENEURS
• People
• Customers
• EmployeeClients
• Businessclients

Answer 4

Your design is fine.

If there were many common elements between employee and business_man , you might need to introduce the supertype / subtype model that sllev points to. If there is not much generality, then a supertype / subtype model may be good for a conceptual model, but impractical for a physical model.

What you do in your design involves using your own type of supertype / subtype, in which there are no common attributes between employees and businessmen, except that you call code_number and an integrated identifier ( Clients.id ). Your splitting attribute is Clients.type .

To make it easier to find information in a single query, define a view that provides UNION connections between Employees and Clients with a connection between Business_Men and Clients .

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