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

这是我的刺:

基本上,您需要将活动关联到1个(联系人或客户)和1个员工,这些员工将是活动的负责人。注意,您可以在这样的模型中处理引用约束。

还要注意,我添加了一个BusinessEntity表,用于连接所有人员和地点。(有时有用,但不必要)。放置BusinessEntity表的原因是,您可以简单地将活动的负责人和接收人引用给BusinessEntity,现在您可以让任何人或所有地方执行和接收活动。

还要注意,我添加了一个BusinessEntity表,用于连接所有人员和地点。(有时有用,但不必要)。放置BusinessEntity表的原因是,您可以简单地将活动的负责人和收件人引用到BusinessEntity,现在您可以让任何人或所有地方执行和接收活动。

这听起来像是一种基本的多对多关系,我会以此为模型。

这个数据库的“正确”设计是每列有一个列,您说您试图避免这种情况。这允许在这两列及其各自的表之间定义适当的外键关系。对引用两个不同表的键使用同一列将使查询难看,并且不能强制执行引用完整性。

活动表应具有外键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

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

如果你不能做到这一点(因为这是建立在现有系统之上的,其设计是固定的),那么你有几个选择。没有一个选择是好的,因为它们不能真正解决最初的缺陷,即客户和联系人分开存储。

1. 使用两列(一个为空)来允许引用完整性工作。

2. 用自己的pk和两列(一列为空)构建一个中间表activitycontacts,以指向客户或联系人。这允许您构建一个“干净”的活动系统,但会将丑陋推到中间表中。(它确实提供了一个可能的好处,即它允许您将活动目标限制为添加到中间表的人员,如果这对您有利的话)。

3. 把最初的设计缺陷带到活动系统中,(我在这里咬舌头)有并行的ContactActivity和CustomerActivity表。要查找员工分配的所有任务,请将这两个表合并为一个视图中的一个表。这允许您维护引用完整性,不需要空列,并为您提供从中获取报告的源。

如果我读得对,接受者就是客户和联系人的概括。
通用规范的设计模式是很好理解的。

Data modeling question

您将拥有如下内容:

Activity | Description | Recipient Type

在哪里? Recipient Type 是其中之一 Contact 或 Customer

然后,您将执行以下SQL select语句:
Select * from table where Recipient_Type = 'Contact';

我知道需要更多的信息。

我们需要一个代表收件人(联系人和客户)的附加表:

此表应如下所示:

ID | Name| Recipient Type

接受者类型 将是本文前面提到的表的关键引用。当然,需要做一些工作来处理这些表之间的级联,主要是更新和删除。因此,要快速回顾一下:

Recipients.Recipient_Type 是一个FK到 Table.Recipient_Type

    [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'

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 | troll@hotmail.com | 232323 | null | ...
1  | hottie | hottie@hotmail.com | 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 | troll@hotmail.com | 232323 | null | ...  
Ask out | Be cool | 1 | hottie | hottie@hotmail.com | 2341241 |null | ...

为另一个实体建模:ActivityRecipientContact和ActivityRecipientCustomer继承的ActivityRecipient,后者将保留正确的客户/联系人ID。

相应的表格如下:

Table: Activities(...., RecipientID)

Table: ActivityRecipients(RecipientID, RecipientType)

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

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

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

我会修改客户和联系人的定义。客户可以是个人,也可以是企业,对吗?在巴西,有“pessoa jurdica”和“pessoa fsica”两个术语,直接(且无意识)翻译成“法人”(商业)和“实体”(个人)。谷歌提出了一个更好的翻译:“法律实体”和“个人”。

因此,我们得到一张个人表,并有一张“LegalEntity”和“Individual”表(如果有足够的属性来证明这一点——这里有很多)。接收器成为一个FK-to-Person表。

联系人去哪儿了?它们变成了一个链接到人的表。因为联系人是另一个人的联系人(例如:我的妻子是我与一些公司的注册联系人,我是客户)。人们可以有联系。

注意:我使用了“person”这个词,但您可以将其称为“customer”来命名该基表。