0

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      |
|...         |           |...       |
philipxy
  • 14,867
  • 6
  • 39
  • 83
P. Lance
  • 179
  • 2
  • 13
  • After I answered I edited your question to improve the title. I then googled it and immediately found [a duplicate answer](https://stackoverflow.com/q/3607783/3404097). Always google many clear concise specific phrasings of your question and tags with & without 'stackoverflow'. (Not some vague or general description of it.) If you don't find an answer then pick one phrasing as your title. I am leaving my answer because it is a different (better) take & specific to your tables. – philipxy Jun 26 '17 at 23:37

1 Answers1

1

A FK (foreign key) holds when subrow values in one place must appear as a CK (candidate key) 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.

Follow a texbook/reference presentation of a published information modeling & DB design method.

philipxy
  • 14,867
  • 6
  • 39
  • 83