sql - 设计带有类型的组和带有类型的项目，其中项目类型与组类型相关

Question

我们有 performers在我们的翻译公司 DB 中，他们远程工作。此时performers是翻译人员和 DTP 专业人员，他们可以有许多服务组，每个服务组可以有许多服务项目。当他们申请我们公司时，他们需要填写表格并选择他们拥有的服务。每个服务组都有一个类型，例如翻译或 DTP。翻译服务组需要源语言和目标语言，而 DTP 服务组不需要。问题是在选择列表上显示服务组的正确服务项目。



此时我有以下设计:



我有 performer_service_group_type和 performer_service_item_type这涉及到 performer_service_group_type用于在 UI 中为所选服务组类型显示正确的服务项目类型。但是我不知道如何通过约束来验证数据库完整性，因为可以毫无问题地保存服务项类型与服务组类型的错误关系。第二个问题是可以为空的 source_language_id和 target_language_id对于某些服务组(如 DTP)

我认为可以呈现的第二种方式是为团体服务提供 2 个单独的表格 - performer_language_service_groups' and performer_misc_service_groups with 2 tables for each for service items and service items types. Here pros that misc service group wouldn't has source_language_id and target_language_id` 但缺点是必须为每个新服务组创建 3 个表，如果我们有一些(送货服务和 express )。

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

Answer 1

这里的问题是，这似乎是一个更大项目的一部分，而且措辞相当困惑。我试图解开这个问题，但我的问题多于答案。但是，您主要关心的似乎是约束，所以这个例子应该会有所帮助。主要问题是对单列键的依赖——本身不一定是坏的，但很难得到正确的约束。

请记住，这是一个合乎逻辑的设计，不会直接解决您所说的问题，但是如果您将其“转换”为 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)

人提供服务，每个人可能提供不止一种服务类型。在该示例中，提供服务的人的通用术语是“表演者”。 Translator 和 dtp-professional 是 performer 父类(super class)型的子类型，descriminator 是服务类型。同一个表演者可以提供不止一种服务类型。

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