两个不同表格中的参考键 [英] Reference key from Either of Two Different Tables

问题描述

我有以下两个表：

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,...)

现在我有另一个表客户端。我的客户可以是类型为 employee 或 business_man 。
所以我有客户端表如下：

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)

类型字段可以存储'employee'或'business_man'，reference_id是Type字段中提到的表的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.

我的问题是：

1. 我做的是对吗？


2. 有更好/高效的方式吗？


3. 如何获得完整的使用单个SQL查询的客户端（给定客户端ID）的详细信息？

---

Raouf Athar

Yarikul

推荐答案

我们使用的稍微不同的方法是创建一个抽象实体表。目的是为所有具体实体提供唯一的序列号。简单的例子如下：

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
)

我们的插入方法将插入到BASE_ENTITY表中，并捕获生成的id值。具体的表（employee，business_person）会将生成的id存储为其PK。其中一个主要的原因是我们的业务，营销，可以让我们移动实体表，当我们了解更多关于他们或重新分类个人。我们发现如果实体478在整个域中是相同的，则它简化了逻辑。而不是根据您的设计中的类型进行查询，因为每个表中都重新定义了一个数字，您只需简单地查询表的连接，如果行返回，则 。 >

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

这篇关于两个不同表格中的参考键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！