使用类型设计组和类型设计项，其中项类型与组类型相关联。

Question

我们的翻译公司数据库中有“执行者”，他们可以远程工作。目前，“执行者”是翻译员和DTP专业人员，他们可以有许多服务组，并且每个服务组都可以有许多服务项目。当他们申请加入我们的公司时，需要填写一个表格并选择他们拥有的服务。每个服务组都有一种类型，例如翻译或DTP。翻译服务组需要源语言和目标语言，而DTP服务组则不需要。问题在于如何为服务组正确显示选择列表的服务项目。

此时我有以下设计：

我有两个变量performer_service_group_type和performer_service_item_type，它们与performer_service_group_type相关联，以在UI中显示所选服务组类型的适当服务项类型。但是，我不知道如何通过约束对其进行验证以实现数据库完整性，因为错误的服务项类型与服务组类型的关系可以毫无问题地保存。第二个问题是一些服务组（如DTP）的可空source_language_id和target_language_id。

我认为第二种方式可以呈现为为组服务拥有2个单独的表- performer_language_service_groups 和 performer_misc_service_groups每个表都有2个表格用于服务项目和服务项目类型。这样misc服务组就不需要source_language_id和target_language_id>了。但缺点是如果我们有一些新的服务组（交付服务和途中的快递员），则需要创建3个表格。

任何建议或建议都将有所帮助，提前感谢。

- Vadim Shvetsov

---

所以基本上你会为一个特定的项目类型卡在两个可空字段和外键验证上。 - downvoteit

对于一个群体，它们都适用；对于另一个群体，它们都不适用。 - Vadim Shvetsov

Answer 1

如果您有一百个服务组，那么我相信您也有许多仅适用于某些组的字段。我认为您需要使用表来存储“允许的字段”以及它们是否是必需的。

<table name="Service Group" comment="for example, DTL">
  <column name="ID" pk="true" />
  <column name="Name" />
</table>

<table name="Service Group Type" comment="for example, Photoshop Markup">
  <column name="ID" pk="true" />
  <column name="Service Group" fk="[Service Group].[ID]" />
  <column name="Name" />
</table>

<table name="List Of Values" comment="for example, Language">
  <column name="ID" pk="true" />
  <column name="Name" />
</table>

<table name="Choice Of List Of Values" comment="for example, Japanese">
  <column name="ID" pk="true" />
  <column name="List Of Values" fk="[List Of Values].[ID]" />
  <column name="Description" />
</table>

<table name="Field" comment="for example, From Language">
  <column name="ID" pk="true" />
  <column name="Name" />
  <column name="List Of Values" fk="[List Of Values].[ID]" />
</table>

<table name="Service Group Field">
  <column name="Service Group" pk="true" fk="[Service Group].[ID]" />
  <column name="Field" pk="true" fk="[Field].[ID]" />
  <column name="Nullable" comment="N means optional, Y means mandatory" />
</table>

<table name="Service Record" comment="only retain core fields here, e.g. Performer, Currency, etc">
  <column name="ID" pk="true" />
  <column name="Service Group Type" fk="[Service Group Type].[ID]" />
  <column name="Performer" fk="[Performer].[ID]" />
  <column name="Currency" fk="[Currency].[ID]" />
  ...
</table>

<table name="Service Record Detail" comment="to organize all conditional fields">
  <column name="ID" pk="true" />
  <column name="Service Record" fk="[Service Record].[ID]" />
  <column name="Field" fk="[Field].[ID]" />
  <column name="Choice" fk="[Choice Of List Of Values].[ID]" />
  <column name="Free Text" />
</table>

问题1通过仅在每个服务记录中存储服务类型来解决。您仍应对屏幕逻辑和预保存逻辑进行验证。为了方便按服务组对服务记录进行分组，您可以创建视图，将数据从服务组类型连接回服务组。

问题2通过可选/必填字段列表来解决。您仍必须添加所需的验证:
1-如果提供了一个值但是无法从[服务组字段]中找到模式，则这是字段的多余输入
2-如果未提供值但是从[服务组字段]中是必填项，则缺少字段的输入

- COY

---

谢谢分享这个。看起来非常有趣，也许比我的现有解决方案更好，但更加复杂和性能较低。无论如何，所有验证最终都将编码处理，并且最好尽可能平铺地存储在数据库中，并通过代码在组内嵌套服务。 - Vadim Shvetsov

你的回答并不是我会使用的解决方案，但实际上我需要选择谁将获得奖励，而你的回答对于我来说似乎更相关以解决这个问题。 - Vadim Shvetsov

事实上，这种结构的性能也不错。您只需要将[服务记录]、[字段]作为聚集索引使用，那么与检索许多列的表相比，差异将非常小。如果这些可配置字段不经常使用（特别是在多个表连接中），它实际上可以提高主表的性能（类似于使用PK创建索引但包括用于快速加载的必要列）。 - COY

Answer 2

Nullability可以通过为SOURCE和TARGET语言（例如source_languages，target_languages）创建2个表来部分解决。在这里，您将添加一个默认值，比如ID=1，VALUE="DEFAULT"，它将对应于您在performer_service_groups上的NULL字段，该字段不再可为空（即从performer_service_groups上删除null标志）。

这样，您将保留内在的SQL外键约束，在DML上，如果您修改performer_service_groups上的source_language_id/target_language_id（分别），它将触发（即如果您想要NULL，则必须添加ID=1来满足您的DML；如果不是，则添加一个适当的ID，并且在任何语言连接到performer_service_groups时，您将始终返回一个值，因此如果您这样做，后端就不需要检查null了；对于UI，如果ID=1或VALUE="DEFAULT"，只需隐藏语言<select>或dropdown即可）。

关于密钥验证，我还在思考中，可能会更新。

我想到了一个密钥验证的方法。如果您为您的2个状态创建一个表，它将基本上反映您的UI界面。这将需要performer_service_groups具有另一个ID字段state_id，它将指向您的状态表。这将强制您在查询中包含该state_id字段，并通过状态过滤您的结果集。基本上是数据的逻辑分割，而不是实际上物理分离数据（只需添加另一维度; 因为每个ID都是新维度，可以在xyz表中旋转或按组结果）。

如果您想要完全严格，您可以在理论上使用BEFORE TRIGGERS或检查约束验证来验证可空字段的DML，但这些将增加开销和不可避免的脆弱性和复杂性。同样的理论可以应用于密钥验证，但我不知道pgSQL触发器在编写代码时有多灵活（从Oracle/PLSQL和MYSQL来）。希望这有意义。

- downvoteit

---

谢谢你的想法。就我个人而言，我不喜欢使用id 1作为默认值的解决方法。这似乎是空值的变形，但这种方法将代码与数据库端的某些硬编码耦合在一起。关于state_id - 这看起来很合理，但对于某些类型的组，管理特殊字段可能会很困难。 - Vadim Shvetsov

Answer 3

这里的问题是，这似乎只是一个更大项目的一部分，措辞 相当混乱。我已经试图解开这个谜团，但我有更多的问题而不是答案。然而，您的主要关注点似乎是 约束，因此这个例子应该有所帮助。主要问题是依赖单列键 -- 这本身并不一定是坏事，但很难正确设置约束。

请记住，这是一个 逻辑设计，它不会直接解决您所述的问题，但如果你“转换”成PostgreSQL，它将会起作用。这样你就可以尝试约束并调整你的项目。

Note:
All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

---

服务类型自定义类型 ID（鉴别器）；对于每个SVC_TYP_ID列使用此类型。

TYPE svc_typ ENUM (T, D)

定义基础概念：服务、服务类型、语言、人员。

-- Service type identified by SVC_TYP_ID,
-- named SVC_TYP_NAME exists.
--
service_typ {SVC_TYP_ID::svc_typ, SVC_TYP_NAME}
         PK {SVC_TYP_ID}
         AK {SVC_TYP_ID}

(T, translation)
(D, dtp)


-- Service named SVC_NAME of type SVC_TYP_ID
-- is identified by SVC_ID.
--
 service_ {SVC_ID, SVC_TYP_ID, SVC_NAME}
      PK {SVC_ID}
      AK {SVC_NAME}
      SK {SVC_ID, SVC_TYP_ID}
      FK {SVC_TYP_ID} REFERENCES service_typ

(WTR, T, written translation)
(EDT, T, editing)
(PRF, T, proof reading)
(IND, D, in-design markup)
(PHT, D, photoshop markup)
(ACD, D, autocad markup)


-- Language name LANG_NAME,
-- identified by LANG_ID exists.
--
lang {LANG_ID, LANG_NAME}
  PK {LANG_ID}
  AK {LANG_NAME}

(EN, English)
(FR, French)
(RU, Russian)


-- Perfomer (person) identified by PERF_ID
-- named FNAME, LNAME exists.
--
perfomer {PERF_ID, FNAME, LNAME}
      PK {PERF_ID}

(1, Lev,  Tolstoy)
(2, Jim,  Blah)
(3, Joe,  Doe)
(4, Jane, Doe)

人们提供服务，每个人可能提供多种服务类型。在这个例子中，提供服务的人的通用术语是“执行者”。翻译和DTP专业人员是执行者超类型的子类型，鉴别器是服务类型。同一执行者可以提供多种服务类型。

-- Perfomer PERF_ID registered for service type SVC_TYP_ID.
--
perf_svc_typ {PERF_ID, SVC_TYP_ID, cols_common_to_all}
          PK {PERF_ID, SVC_TYP_ID}
         FK1 {PERF_ID}    REFERENCES perfomer
         FK2 {SVC_TYP_ID} REFERENCES service_typ

(1, T, ... )
(2, T, ... )
(3, D, ... )
(4, T, ... )
(4, D, ... ) -- PERF_ID = 4 does translations and dtp


-- Performer PERF_ID is registered as a translator.
--
-- note: (SVC_TYP_ID = T)
--
translator {PERF_ID, SVC_TYP_ID, cols_specific_to_translators}
        PK {PERF_ID}
     CHECK (SVC_TYP_ID = T::svc_typ)

        FK {PERF_ID, SVC_TYP_ID} REFERENCES perf_svc_typ

(1, T, ...)
(2, T, ...)
(4, T, ...)


-- Performer PERF_ID is registered as a DTP professional.
--
-- note: (SVC_TYP_ID = D)
--
dtp_prof {PERF_ID, SVC_TYP_ID, cols_specific_to_dtp_prof}
      PK {PERF_ID}
   CHECK (SVC_TYP_ID = D::svc_typ)

          FK {PERF_ID, SVC_TYP_ID} REFERENCES
perf_svc_typ {PERF_ID, SVC_TYP_ID}

(3, D, ...)
(4, D, ...)

翻译人员可能能够提供多种语言的服务。

-- Performer PERF_ID, who registered as a translator,
-- speaks language LANG_ID.
--
perf_lang {PERF_ID, LANG_ID}
       PK {PERF_ID, LANG_ID}

      FK1 {PERF_ID} REFERENCES translator
      FK2 {LANG_ID} REFERENCES lang

(1, EN)
(1, FR)
(1, RU)
(2, EN)
(2, FR)
(4, FR)
(4, RU)

每个人（执行者）可以提供多组服务。每个服务组中的服务必须是相同的服务类型。执行者必须注册为此服务类型的提供者。

-- Performer PERF_ID offers a group of services,
-- identified by (PERF_ID, PERF_GROUP_NO);
-- each service in the group is of type SVC_TYP_ID.
--
svc_group {PERF_ID, PERF_GROUP_NO, SVC_TYP_ID}
       PK {PERF_ID, PERF_GROUP_NO}
       SK {PERF_ID, PERF_GROUP_NO, SVC_TYP_ID}
       FK {PERF_ID, SVC_TYP_ID} REFERENCES perf_svc_typ

(1, 1, T)
(1, 2, T)
(2, 1, T)
(3, 1, D)
(4, 1, T)
(4, 2, D)

每个表演者的服务组列出了该组表演者提供的服务类型的服务。

-- Performer PERF_ID offers translation (SVC_TYP_ID = T)
-- service SVC_ID from FROM_LANG to TO_LANG,
-- in that performer's service group
-- identified by (PERF_ID, PERF_GROUP_NO)
--
trans_svc {
         PERF_ID
       , PERF_GROUP_NO
       , SVC_ID
       , SVC_TYP_ID
       , FROM_LANG
       , TO_LANG
       }

PK {PERF_ID, PERF_GROUP_NO, SVC_ID, FROM_LANG, TO_LANG}

CHECK (SVC_TYP_ID = T::svc_typ)

FK1 {PERF_ID, PERF_GROUP_NO, SVC_TYP_ID} REFERENCES svc_group
FK2 {SVC_ID, SVC_TYP_ID} REFERENCES service_
FK3 {PERF_ID} REFERENCES  translator

      FK4 {PERF_ID, FROM_LANG} REFERENCES
perf_lang {PERF_ID, LANG_ID}

      FK5 {PERF_ID, TO_LANG} REFERENCES
perf_lang {PERF_ID, LANG_ID}


(1, 1, WTR, T, EN, RU) -- for translation from <> to
(1, 1, WTR, T, FR, RU)
(1, 2, PRF, T, RU, RU) -- for edit and proof from = to
(1, 2, EDT, T, RU, RU)
(1, 2, PRF, T, EN, EN)
(2, 1, WTR, T, EN, FR)
(2, 1, WTR, T, FR, EN)
(2, 1, EDT, T, EN, EN)
(2, 1, PRF, T, EN, EN)
(2, 1, PRF, T, FR, FR)
(4, 1, WTR, T, FR, RU)
(4, 1, PRF, T, FR, FR)

-- Performer PERF_ID offers DTP (SVC_TYP_ID = D) service_ SVC_ID
-- in group identified by (PERF_ID, PERF_GROUP_NO).
--
dtp_svc {PERF_ID, PERF_GROUP_NO, SVC_ID, SVC_TYP_ID}
     PK {PERF_ID, PERF_GROUP_NO, SVC_ID}

  CHECK (SVC_TYP_ID = D::svc_typ)

    FK1 {PERF_ID, PERF_GROUP_NO, SVC_TYP_ID} REFERENCES svc_group 

    FK2 {SVC_ID, SVC_TYP_ID} REFERENCES service_ 

    FK3 {PERF_ID} REFERENCES  dtp_prof

(3, 1, PHT, D)
(3, 1, ACD, D)
(4, 2, IND, D)
(4, 2, ACD, D)

- Damir Sudarevic

---

感谢您的时间。我会使用表格而不是枚举来避免为我们需要的每个新服务组类型开发。如果我只有几个服务组，您的建议很好，但最终它们将超过100个，每次为每个服务组类型编写约束都很痛苦。我希望能找到更抽象的东西，或者坚持在服务器级别上进行验证，而不是在数据库级别上进行验证。 - Vadim Shvetsov

Answer 4

目前我将坚持以下解决方案。

如果我将拥有一个新的组，其中包含特定服务组的特殊字段，我会创建一个具有1-1关系的新表，并将布尔字段添加到service_group作为获取某些内容的必要标志。也许以后我会采用更动态的方式。如果有人仍然有改进此模式的想法 - 我很想知道 :)

- Vadim Shvetsov