如何表示分类的多对多数据库关系，其中类别特定于关系的一侧 [英] How to represent a categorized many-to-many database relationship where categories are specific to one side of the relationship

问题描述

我正在建立一个数据库来管理项目分配，而给我特别麻烦的一个部分是为每个正在处理的项目的员工分配职位。

需求

1. 员工一次可以在多个项目上


2. 一个项目有多个员工


3. 一个项目有多个职位名称


4. 员工在一个项目下工作，


5. 多个员工可以在同一个专案中工作。

确定如何使用表来表示;我想出的每一个布局都使得一个员工可以在一个不同项目的工作项目下工作，或者他们能够在两个不同的职位下工作同一个项目。

示例图

基本上，我有三个表：

表

• 项目
  
  
  
  
  • 项目名称（唯一）
  
  
  • 项目ID
  
  


• 员工
  
  
  
  
  • 员工姓名（唯一）
  
  
  • 员工ID
  
  


• 标题
  
  
  
  
  • 标题
  
  
  • 项目ID（Title-ProjectID unique）
  
  
  • 标题ID
  
  

然后交叉引用表，称为Assignments。到目前为止，我分配的两种方式如下：

示例1

• 作业
  
  
  
  
  • 员工ID
  
  
  • 专案ID（EmployeeID-ProjectID unique）
  
  
  • 标题ID（唯一）
  
  
  • AssignmentID
  
  

这种方式将员工限制为每个项目一个标题，但允许他们在作业中使用不属于项目的标题。

示例2

• 分配
  
  
  
  
  • Employee ID
  
  
  • 标题ID（EmployeeID-TitleID unique）
  
  
  • AssignmentID
  
  

这种方式通过标题将员工分配给项目，因此不可能将某人分配给项目无效标题。

同样，这些图也可以在这里找到： http://i.imgur.com/IbR0P.png

我知道必须有一种方法来干净地做到这一点，但是我没有在数据库设计中进行任何真正的正式培训，我通过我的搜索找不到任何东西，除了如何做一个多对多的关系，这不是完全我需要帮助。

谢谢！

• 编辑1
  
  
  
  
  • 加粗的主键字段（在图表图片中加了下划线，但很难区分，因为它们是最后一个字段）
  
  
  • 添加了AssignmentID
  
  
  • 编辑2
    
    
    
    
    • 添加了缺少的要求（5）
    
    
    • 添加了示例和要求的标题
    
    
  
  
  • 编辑3
    
    
    
    
    • 现在我有10个代表， / li>
      
  
  
  • 编辑4
    
    
    
    
    • 添加了唯一键标识符
    
    
  
  
  
  

  解决方案

  为您职位名称添加ID字段 - 让我们称之为 JobTitleID

  
  
  

  现在您的分配表有员工ID 和 JobTitleID 。

  
  
  

  这就是为了找到员工的项目，通过分配表和 职位名称表

  
  
  

  EDIT

  
  
  

  在评论中讨论后，请忽略上述内容， p>
  
  

  现在这里是新版本：您的分配表需要（如您已经考虑的）

  
  
  
  
  
  • 员工ID
  
  
  • 项目ID
  
  
  • 标题ID
  
  
  • AssignmentID
  
  
  
  
  

  但它还需要 UNIQUE INDEX（EmployeeID，ProjectID） - 这将使一个员工不能在不同标题下的同一项目中。

  
  
  

  仍然允许同一标题下的多个员工，以及一个员工的不同项目中的多个标题。

  
  

  I'm working on building a database to manage project assignments, and the one part that's giving me particular trouble is the assignment of job titles to employees with for each project they are working on.

  Requirements

  1. An Employee can be on multiple Projects at a time
  2. A Project has multiple Employees on it
  3. A Project has multiple Job Titles
  4. An Employee works on a Project under exactly one of the Project's Job Titles
  5. Multiple Employees can work under the same Job Title in a Project

  I'm not sure how to represent this using tables; every layout I come up with either makes it possible for an employee to work on a project under a job title from a different project, or they are able to work on the same project under two different job titles.

  Example Diagrams

  Basically, I have three tables:

  Tables

  • Projects
    • Project Name (unique)
    • Project ID
  • Employees
    • Employee Name (unique)
    • Employee ID
  • Job Titles
    • Title
    • Project ID (Title-ProjectID unique)
    • Title ID

  And then a cross-reference table, called Assignments. The two ways I have come up with so far for Assignments are as follows:

  Example 1

  • Assignments
    • Employee ID
    • Project ID (EmployeeID-ProjectID unique)
    • Title ID (unique)
    • AssignmentID

  This way limits employees to one title per project, but allows them to use a title that doesn't belong to the project in the assignment.

  Example 2

  • Assignments
    • Employee ID
    • Title ID (EmployeeID-TitleID unique)
    • AssignmentID

  This way assigns employees to the project through the title, so it is impossible to assign someone to a project with an invalid title. However, this allows an employee to be assigned to the same project under multiple titles.

  Again, the diagrams are available here: http://i.imgur.com/IbR0P.png

  I know there must be a way to do this cleanly, but I haven't had any real formal training in database design and I can't find anything through my searches except how to make a many-to-many relationship, which isn't exactly what I need help with.

  Thanks!

  • EDIT 1
    • Bolded Primary Key Fields (were underlined in diagram image, but hard to tell since they are the last fields)
    • Added AssignmentID (Primary Key) to Assignments table in the question (was present in designs, forgot to include when creating question & diagram)
  • EDIT 2
    • Added missing requirement (5)
    • Added headers for examples and requirements
  • EDIT 3
    • I have 10 rep now, so I can put up the diagram!
  • EDIT 4
    • Added identifiers for unique keys (individual unique keys identified in diagram, but I don't know how to do compound keys in DIA)

  解决方案

  Add an ID field to you Job Titles table - let's call it JobTitleID

  Now your Assignments table has Employee ID and JobTitleID.

  This ofcourse means, that to find the Projects for an employee, you need to join through the Assignments table and the Job Titles table

  EDIT

  After discussion in the comments, please disregard the above, I left it only as history.

  Now here is the new version: Your Assignments table needs (as you already considered)

  • Employee ID
  • Project ID
  • Title ID
  • AssignmentID

  But it also needs a UNIQUE INDEX(EmployeeID, ProjectID) - this will make it impossible for one employee to be in the same project under different titles.

  Multiple employees under the same title will still be allowed, as well as multiple titles in different projects for one employee.

  这篇关于如何表示分类的多对多数据库关系，其中类别特定于关系的一侧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！