用于处理员工能力的SQL体系结构设计

Question

忍者，我知道这可能是一个“方法太广”或“错误的门户”类型的问题，但这样的感觉就像家，所以我会尝试一下。

我有一张有员工的桌子

Table: employee
id, name
1 - John
2 - Jane
3 - Obama
4 - Donald

...nothing的花哨。然后是能力表(特殊任务/责任的分类器)。

competencies table:
id, name
1 - Janitor
2 - Sysadmin
3 - Programmer
4 - Pilot
...

每个员工可以有多个能力(关系表)

表: employee_competency

id, employee_id, competency_id
1 - 1 - 1  - John is a Janitor
2 - 1 - 2  - John is also a Sysadmin (imagine that)
3 - 2 - 3  - Jane is a Programmer
4 - 3 - 3  - Obama is a Programmer
5 - 3 - 4  - ...and a Pilot
6 - 4 - 1  - Donald is a Janitor

数据库体系结构存在的问题或如何处理这种情况。

1. 我希望能够定义无限数量的能力，这些能力可能因客户的不同而不同(我正在编程的项目将在其中安装-每个项目安装可以有不同的能力集)。
2. 在代码中，我希望能够选择具有特定能力的员工(例如-列出所有飞行员的员工).

通过在列出员工时对能力ID进行硬编码，我失去了自由定义能力的能力。我可以在employee表中定义自定义字段，如is_janitor、is_sysadmin、is_programmer、is_pilot等.但我失去了定义无限能力的能力.

有办法用不同的DB体系结构方法来解决这个XY问题吗？

Answer 1

这里的关键思想是，您必须有一个列表，使您可以选择一个能力，以及数据驱动。因此，当您在选择要列出的能力的屏幕/窗体/页面上时，通过数据库中的能力表驱动该选择，将该能力的ID作为选择的值传递回您的查询，以便您可以按能力查询员工列表。

您不应该将单独的ID放入系统中。现在，这变得复杂，当你有行为，你想要驾驶根据能力。这需要更高层次的抽象思考。例如，假设您有一个表单，您希望在其中显示另一个选项卡，以允许客户选择飞行员获得认证的飞机。为此，我通常创建实际定义驱动行为(如CAN_SELECT_PLANES)的标志，以添加到相关表中。此表定义系统的功能，而不是能力的功能。保持这种抽象是很重要的，因为客户会想要改变他们的能力名称，您稍后会发现该特性的新用途。

Answer 2

若要从数据库中选择所有程序员，请使用例如：

SELECT
 e.name AS empl_name,
 c.name AS comp_name
FROM 
  employee_competency ec,
  competencies c,
  employee e
WHERE
  c.id=ec.competency_id
AND
  e.id=ec.employee_id
AND
  c.id=3