I have the following situation. My table is:
我有以下情况。我的表是:
Table: CompanyEmployees
表:CompanyEmployees
- EmployeeID
- EmployeeID
- Date of Birth
- 出生日期
- Date Joined
- 加入日期
I also want to store the sales information for each employee. I have this:
我还想为每个员工存储销售信息。我有这个:
Table: DealsCompleted
表:DealsCompleted
- ID
- ID
- EmployeeID
- EmployeeID
- Deal Name
- 交易名称
- Deal Amount
- 交易金额
My question is this- should there be a column in CompanyEmployees called "DealsCompletedID" which directly refers to the ID column in DealsCompleted, or is it acceptabe to just create a foreign key between the two Employee ID columns? Does this disadvantage the design or potentially distort the normalization?
我的问题是——应该在公司员工中有一个名为“DealsCompletedID”的列，直接引用DealsCompleted中的ID列，还是仅仅在两个员工ID列之间创建一个外键就可以了?这是设计上的缺陷，还是可能会扭曲规范化?
I am unclear what the rule is as to whether I should include an extra column in CompanyEmployees or not.
我不清楚我是否应该在公司员工中增加一个专栏的规定是什么。
EDIT Please assume there will only be one row in the deal table, per employee.
编辑请假设在交易表中，每个员工只有一行。
2 个解决方案
#1
2
A FOREIGN KEY should point from one table to its referenced row in a parent table, the two tables should generally not reference each other (with foreign keys defined in both).
外键应该从一个表指向父表中引用的行，这两个表通常不应该相互引用(两个表中都定义了外键)。
The FOREIGN KEY is most appropriately defined in the DealsCompleted table, which points back to CompanyEmployees.EmployeeID. Think about it this way - The CompanyEmployees table stores information about employees. Deals they completed do not really count as information about employees. However, the employee who completed a deal is a part of the information about a deal, so the key belongs there.
在DealsCompleted表中，最恰当地定义了FOREIGN KEY，该表指向公司员工。以这种方式思考——公司员工表存储有关员工的信息。他们完成的交易并不能算作雇员的信息。然而，完成交易的员工是交易信息的一部分，所以关键就在那里。
Having DealsCompleted.EmployeeID will allow for a proper one to many relationship between employees and deals. That is, one employee can have as many related rows in DealsCompleted as needed. Including a DealsCompleted column in the CompanyEmployees table on the other hand, would require you to either duplicate rows about employees, which breaks normalization, or include multiple DealCompletedID values in one column which is also incorrect.
DealsCompleted。雇员互诫将允许员工和交易之间有一个适当的关系。也就是说，一个员工可以根据需要在DealsCompleted中包含尽可能多的相关行。另一方面，在CompanyEmployees表中包含DealsCompleted列，将要求您要么复制关于employees的行，这会破坏规范化，要么在一个列中包含多个DealCompletedID值，这也是不正确的。
Update after edit above Even if you plan for only a one-to-one relationship (one deal per employee), it is still more appropriate to reference the EmployeeID in DealsCompleted rather than the other way around (or both ways). ...And it allows you to expand to one-to-many, when the need arises.
即使你只计划一对一的关系(每个员工一笔交易)，在DealsCompleted中引用EmployeeID，而不是相反的方式(或者两种方式)，在上面的编辑之后进行更新。当需要时，它允许你扩展到一对多。
#2
1
Assuming that the relationship will always be one-to-one, as you state, then the answer depends on what is the primary entity within the Domain Model. If this database is at its core a database about Deals, and employee data is ancillary, then I would add an EmployeeId FK column in the Deal table. If otoh, this is a database about Employees, and Deals are ancillary, then eliminate the EmployeeId column in the Deal table, and add a DealId FK column to the Employeee table.
假设在声明时关系总是一对一的，那么答案取决于域模型中的主要实体是什么。如果这个数据库的核心是一个关于交易的数据库，而员工数据是辅助的，那么我将在交易表中添加EmployeeId FK列。如果otoh是一个关于雇员的数据库，并且交易是辅助的，那么删除交易表中的EmployeeId列，并向Employeee表添加DealId FK列。
#1
2
A FOREIGN KEY should point from one table to its referenced row in a parent table, the two tables should generally not reference each other (with foreign keys defined in both).
外键应该从一个表指向父表中引用的行，这两个表通常不应该相互引用(两个表中都定义了外键)。
The FOREIGN KEY is most appropriately defined in the DealsCompleted table, which points back to CompanyEmployees.EmployeeID. Think about it this way - The CompanyEmployees table stores information about employees. Deals they completed do not really count as information about employees. However, the employee who completed a deal is a part of the information about a deal, so the key belongs there.
在DealsCompleted表中，最恰当地定义了FOREIGN KEY，该表指向公司员工。以这种方式思考——公司员工表存储有关员工的信息。他们完成的交易并不能算作雇员的信息。然而，完成交易的员工是交易信息的一部分，所以关键就在那里。
Having DealsCompleted.EmployeeID will allow for a proper one to many relationship between employees and deals. That is, one employee can have as many related rows in DealsCompleted as needed. Including a DealsCompleted column in the CompanyEmployees table on the other hand, would require you to either duplicate rows about employees, which breaks normalization, or include multiple DealCompletedID values in one column which is also incorrect.
DealsCompleted。雇员互诫将允许员工和交易之间有一个适当的关系。也就是说，一个员工可以根据需要在DealsCompleted中包含尽可能多的相关行。另一方面，在CompanyEmployees表中包含DealsCompleted列，将要求您要么复制关于employees的行，这会破坏规范化，要么在一个列中包含多个DealCompletedID值，这也是不正确的。
Update after edit above Even if you plan for only a one-to-one relationship (one deal per employee), it is still more appropriate to reference the EmployeeID in DealsCompleted rather than the other way around (or both ways). ...And it allows you to expand to one-to-many, when the need arises.
即使你只计划一对一的关系(每个员工一笔交易)，在DealsCompleted中引用EmployeeID，而不是相反的方式(或者两种方式)，在上面的编辑之后进行更新。当需要时，它允许你扩展到一对多。
#2
1
Assuming that the relationship will always be one-to-one, as you state, then the answer depends on what is the primary entity within the Domain Model. If this database is at its core a database about Deals, and employee data is ancillary, then I would add an EmployeeId FK column in the Deal table. If otoh, this is a database about Employees, and Deals are ancillary, then eliminate the EmployeeId column in the Deal table, and add a DealId FK column to the Employeee table.
假设在声明时关系总是一对一的，那么答案取决于域模型中的主要实体是什么。如果这个数据库的核心是一个关于交易的数据库，而员工数据是辅助的，那么我将在交易表中添加EmployeeId FK列。如果otoh是一个关于雇员的数据库，并且交易是辅助的，那么删除交易表中的EmployeeId列，并向Employeee表添加DealId FK列。