c# - 以下场景的数据库设计

Question

我正在努力为以下要求创建数据库架构:员工具有一定的属性。这些相同的属性也存在于工作中。我需要做的是根据给定工作的属性搜索员工。例如，“为我查找与职位 ID = 1 匹配且具有相同属性的所有员工”。 (反之亦然，用于搜索给定员工的所有职位)。这是我的数据库架构。

Job (
   jobId, 
   description
)

Employee(
  employeeId, 
  firstName, 
  lastName,
  startDate,
  email
)

Attribute(
   attributeId,
   description       
)

JobAttribute(
  jobId, (FK to Jobs.jobId)
  attributeId (FK to Attributes.attributeId)
)

EmployeeAttribute(
   employeeId, (FK to Employees.employeeId)
   attributeId (FK to Attributes.attributeId).       
)

查询“为我查找与职位 ID = 1 匹配且具有相同属性的所有员工”。然后看起来像:

SELECT DiSTINCT e.employeeId 
FROM Employee e
INNER JOIN EmployeeAttribute ea
ON e.attributeId = ea.AttributeId
and e.employeeId = ea.employeeId
WHERE ea.AttributeId In (
Select ja.attributeId 
FROM Job j
inner join JobAttribute ja
    on j.jobId = ja.jobId
inner join AttributeTable at
    on ja.attributeId = at.attributeId
where jobId = 1
)
order by e.startDate asc

该查询将返回至少有一个与该职位相同的属性的所有员工。

如果我需要返回根据使用此架构的作业分配了所有属性的所有员工，查询会是什么样子？

对于查询性能而言，这种设计是否良好？我还希望使其尽可能灵活，以允许以最少的代码更改添加新属性。

Answer 1

好的，您的第一个查询“为我查找与职位 id = 1 匹配且具有相同属性的所有员工”，可能会更简单，如下所示

SELECT DISTINCT E.EmployeeId
FROM 
Employee E
INNER JOIN EmployeeAttribute EA ON (EA.EmpId = E.EmployeeId)
INNER JOIN JobAttribute JA ON (EA.AttributeId = JA.AttributeId)
WHERE AJ.JobId = 1

关于检索与给定职位的所有属性匹配的员工的查询

SELECT EA.EmployeeId, COUNT(EA.EmployeeId)
FROM EmployeeAttribute EA
INNER JOIN JobAttribute JA on EA.AttributeId = JA.AttributeId
WHERE JA.JobId = @jobId
GROUP BY EA.EmployeeId
HAVING COUNT(EA.EmployeeId) >= (SELECT COUNT(AttributeId) FROM JobAttribute J where J.JobId = @jobId)

您可能需要加入员工主人才能获取员工的其他详细信息。这两个查询都假设派生表上存在一个 PK，它是两个 FK 的组合。

就性能而言，请使用查询执行计划并相应地定义索引。