什么外键添加在1：N的关系 - 逻辑模型 [英] What foreign keys to add in 1:N relationship - Logical Model

问题描述

我们有两个表：员工和部门。每个员工都知道自己在哪个部门工作，并且每个部门都知道员工在哪里工作 - 换句话说就是一系列员工标识符。

查看，我是否通过在两个表中添加FK来标记？

  | EMPLOYEE | |部| 
 | ____________ | | __________ | 
 | PK EMP_ID |> O ------ ||  -  | PK DEPT_ID | 
 | FK DEPT_ID | | FK EMP_ID | 
 | NAME | | NAME | 
 | ... | | ... | 
  

解决方案

一个地方必须出现在别的地方。

这取决于你在什么表格中想要什么信息，也就是说每个表格代表什么样的商业关系/关联。 之后，你有你的餐桌意义，什么样的商业情况可以出现&表的含义决定了数据库状态可能出现的情况，即约束是什么以及基数是什么。我们告诉DBMS，这样可以防止更新状态不能出现。

在这种情况下，部署emp_id意味着什么？如果表的含义（具有特征谓词）department dept_id 具有employee emp_id ...，则（a）dept_id在Employee& amp ; （b）dept_id不是部门和行政部门的PK。 （c）部门违反正常化原则。如果表格的意思是department dept_id 有manager emp_id ...，那么PK / FK是有意义的。但是，那么你碰巧遇到了典型的SQL DBMS（不必要的），不处理FK周期。但是你似乎只想部门名称​​ dept_id 具有名称 ...。所以没有部门的emp_id列。或者，也许你想要Employee&部门没有彼此的id和Works_in（emp_id，dep_id）employee emp_id 在部门dept_id 中工作（涉及FKs？）。

PS知道不是一个有用的概念。 （你似乎怀疑这一点，因为你把它放在吓人的引语中。）你似乎试图捕捉到你有一些含义，涉及雇员和雇员。部门。决定你需要什么谓词来记录你的情况。 FK持有的情况是，如果某些价值观满足了某种关系/关联，那么它们也满足了某种其他的关系/关联。用来表示FK的关系，虽然非常普遍，但是由于对关系模型和ER建模的误解而产生。

I'm confused about marking down bidirectional 1:N relationship in a logical model.

We have two tables: Employee and Department. Every employee "knows" what department he's working at and every department "knows" what employees work there - in other words a collection of Employee identifiers.

From a formal point of view, do I mark this down by adding FK to both tables?

|EMPLOYEE    |           |DEPARTMENT|
|____________|           |__________|
|PK EMP_ID   |>O------||-|PK DEPT_ID|
|FK DEPT_ID  |           |FK EMP_ID |
|NAME        |           |NAME      |
|...         |           |...       |

解决方案

A FK (foreign key) holds when subrow values in one place must appear somewhere else.

It's up to you do decide what info you want in what tables, ie what business relationship/association you want each table to represent. After you have your table meanings, what business situations can arise & the table meanings determine what database states can arise, ie what the constraints are and what the cardinalities are. We tell the DBMS so it can prevent updates to states that cannot arise.

In this case, what would it mean to have emp_id in Department? If the table means (has characteristic predicate) "department dept_id has employee emp_id..." then (a) dept_id is redundant in Employee & (b) dept_id is not a PK in Department & (c) Department violates normalization principles. If the table means "department dept_id has manager emp_id..." then the PK/FK make sense. But then you happen to run afoul of typical SQL DBMSs (needlessly) not dealing with FK cycles. But you seem to just want "department dept_id has name name...". So no emp_id column in Department. Or maybe you want Employee & Department without each other's ids plus Works_in(emp_id, dep_id) "employee emp_id works in department dept_id" (which involves what FKs?).

PS "Knows" is not a helpful concept. (You seem to suspect this since you put it in scare quotes.) You seem to be trying to capture that you have some meaning(s) in mind involving both employees & departments. Decide what predicates you need to record your situations. What is going on when a FK holds is that given some meanings you picked, if certain values satisfy a certain relationship/association then they also satisfy a certain other relationship/association. "Relationship" used to mean FK, although very common, arises from misunderstanding of the relational model and ER modeling.

这篇关于什么外键添加在1：N的关系 - 逻辑模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！