设计这个特定数据库/SQL 问题的最佳方法是什么？

Question

这是一个一直困扰我们的棘手的规范化/SQL/数据库设计问题。我希望我能正确表述。

您有一组活动。它们是需要完成的事情——一份美化的待办事项列表。任何给定的活动都可以分配给员工。

每个活动还有一个要为其执行该活动的实体。这些活动是联系人（个人）或客户（企业）。每个活动都会有一个联系人或一个客户来为其完成该活动。例如，活动可能是“向 Spacely Sprockets（客户）发送感谢卡”或“向 Tony Almeida（联系人）发送营销资料”。

从该结构中，我们需要能够查询以找到给定员工必须执行的所有活动，将它们列出在一个关系中，最简单的形式如下所示：

-----------------------------------------------------
| Activity | Description    | Recipient of Activity |
-----------------------------------------------------

这里的想法是避免有两列对于联系人和客户，其中之一为空。

我希望我的描述是正确的，因为这并不像乍一看那么明显。

所以问题是：什么是数据库的“正确”设计以及如何查询它以获得所需的信息？

Answer 1

这听起来像是一个基本的多对多关系，我会这样建模。

Answer 2

该数据库的“正确”设计是每个数据库都有一列，您说您正在努力避免这种情况。这允许在这两列及其各自的表之间定义正确的外键关系。对引用两个不同表的键使用同一列将使查询变得丑陋，并且您无法强制执行引用完整性。

活动表应具有外键 ContactID、CustomerID

要显示员工的活动：

SELECT ActivityName, ActivityDescription, CASE WHEN a.ContactID IS NOT NULL THEN cn.ContactName ELSE cu.CustomerName END AS Recipient
FROM activity a
LEFT JOIN contacts cn ON a.ContactID=cn.ContactID
LEFT JOIN customers cu ON a.CustomerID=cu.CustomerID

Answer 3

我不清楚为什么您将客户和联系人定义为单独的实体，因为它们似乎是同一实体的版本。在我看来，客户是具有附加信息的联系人。如果可能的话，我会创建一个联系人表，然后使用该表中的字段来标记客户，或者通过将其 id 添加到其中包含扩展单例客户信息的客户表中来标记客户。

如果您无法做到这一点（因为这是构建在设计固定的现有系统之上），那么您有多种选择。所有选择都不好，因为它们无法真正解决原始缺陷，即单独存储客户和联系人。

1. 使用两列（一列为 NULL）以允许引用完整性发挥作用。

2. 构建一个中间表 ActivityContacts，它有自己的 PK 和两列（其中一列为 NULL）以指向客户或联系人。这允许您构建一个“干净的”活动系统，但将丑陋推入中间表中。 （它确实提供了一个可能的好处，那就是它允许您将活动的目标限制为添加到中间表中的人员，如果这对您来说是一个优势）。

3. 将原始设计缺陷带入“活动”系统中，并且（我在这里忍住了）拥有并行的“ContactActivity”和“CustomerActivity”表。要查找分配给员工的所有任务，请将这两个表合并为一个视图中的一个表。这使您能够保持引用完整性，不需要 NULL 列，并为您提供获取报告的来源。

Answer 4

我的观点如下：

基本上，您需要将活动与 1 名（联系人或客户）和 1 名负责该活动的员工相关联。请注意，您可以在这样的模型中处理引用约束。

另请注意，我添加了一个连接所有人员和地点的businessEntity 表。 （有时有用但不是必需的）。放置businessEntity 表的原因是您可以简单地将活动中的负责人和接收者引用到businessEntity，现在您可以让任何人和所有人员或地点执行和接收活动。

Answer 5

如果我没看错的话，收件人是客户和联系人的概括。
gen-spec 设计模式很好理解。

数据建模问题

Answer 6

您将得到如下所示的内容：

Activity |描述 |收件人类型

其中收件人类型 是联系人 或客户 之一，

然后您将执行SQL 选择语句，如下所示：
Select * from table where Recipient_Type = 'Contact';

我意识到需要更多信息。

我们需要一个额外的表来代表收件人（联系人和客户）：

该表应如下所示：

ID |姓名|收件人类型

收件人类型 将是对本文前面提到的表的关键引用。当然，需要做一些工作来处理这些表之间的级联，主要是更新和删除。快速回顾一下：

Recipients.Recipient_Type 是 Table.Recipient_Type 的 FK

Answer 7

Answer 8

    [ActivityRecipientRecipientType]
    ActivityId
    RecipientId
    RecipientTypeCode
        |||   |||  |||_____________________________    
         |     |                                  |
         |     --------------------               |
         |                        |               |
    [Activity]                [Recipient]      [RecipientType]
    ActivityId                RecipientId      RecipientTypeCode
    ActivityDescription       RecipientName    RecipeintTypeName


    select 
      [Activity].ActivityDescription  
    , [Recipient].RecipientName
    from
      [Activity] 
  join [ActivityRecipientRecipientType] on [Activity].ActivityId = [ActivityRecipientRecipientType].ActivityId
  join [Recipient] on [ActivityRecipientRecipientType].RecipientId = [Recipient].RecipientId
  join [RecipientType] on [ActivityRecipientRecipientType].RecipientTypeCode = [RecipientType].RecipientTypeCode
  where [RecipientType].RecipientTypeName = 'Contact'

Answer 9

Actions
Activity_ID | Description | Recipient ID
-------------------------------------
11    | Don't ask questions | 0
12    | Be cool    | 1

Activities
ID | Description
----------------
11  | Shoot
12  | Ask out

People
ID | Type | email | phone | GPS |....
-------------------------------------
0  | Troll | [email protected] | 232323 | null | ...
1  | hottie | [email protected] | 2341241 | null | ...


select at.description,a.description, p.* from Activities at, Actions a, People p
where a."Recipient ID" = p.ID 
  and at.ID=a.activity_id

result:

Shoot | Don't ask questions | 0 | Troll | [email protected] | 232323 | null | ...  
Ask out | Be cool | 1 | hottie | [email protected] | 2341241 |null | ...

Answer 10

对另一个实体建模：ActivityRecipient，它将由 ActivityRecipientContact 和 ActivityRecipientCustomer 继承，它将保存正确的客户/联系人 ID。

相应的表将是：

Table: Activities(...., RecipientID)

Table: ActivityRecipients(RecipientID, RecipientType)

Table: ActivityRecipientContacts(RecipientID, ContactId, ...,ExtraContactInfo...)

Table: ActivityRecipientCustomers(RecipentID, CustomerId, ...,ExtraCustomerInfo...)

这样您还可以为每种收件人类型设置不同的其他列

Answer 11

我将修改客户和联系人的定义。客户可以是个人也可以是企业，对吗？在巴西，有“pessoa jurídica”和“pessoa física”这两个术语，直接（无意识地）翻译成“法人”（企业）和“自然人”（个人）。谷歌建议了一个更好的翻译：“法人实体”和“个人”。

因此，我们得到一个人员表，并有一个“LegalEntity”和“Individual”表（如果有足够的属性来证明它的合理性 - 这里有很多）。接收者成为 FK to Person 表。

联系人又到哪里去了？它们成为与人链接的表格。由于联系人是另一个人的联系人（例如：我的妻子是我在某些公司的注册联系人，我是客户）。人们可以有联系。

注意：我使用了“Person”一词，但您可以将其称为“Customer”来命名该基表。