Geek Answers Handbook

Numerous relationships between two entities - is this a good practice?

I have the following typical scenario regarding the office and its employees:

  • Each employee belongs to one office.
  • Each office has only one manager (employee)

ER model

er

As you can see, this leads to the fact that the relationship is written twice: once, when the foreign key in the office table points to the manager, as well as in the personnel table pointing to the office where the employee works.

I was looking for alternative ways to simulate this, but still a little lost. Maybe someone will advise a suitable way of modeling or if my method is acceptable for the script.

Many thanks

+1
source share
5 answers

This is not that “relationships [double-written”], but you actually have two relationships between these tables - which is great. My only concern is whether the manager can belong to the same office that he manages? (And, accordingly: is it true that each employee has an office, and each office has a manager who is an employee?) If so, you have a cyclical dependence: you cannot establish a manager’s office until an office will appear, but you cannot set an office manager until a manager appears. As long as one or the other field is null, you can bypass this by application logic ( INSERTone, then INSERTother, and thenUPDATE ), . , , .

+2

, , , . , , , , . , , .

+1

, , SQL, .

, , . , , , .

Office.Manager Staff.IsManager, , false .

+1

, . , . - , , .

, NULL - , , , . , .

, , , "", .

0

, . , .

staffid ->officeid -> manager -> staffid

This cycle can happen when data is extracted (I encountered the same situation once), so it is better to normalize this and avoid this problem with two relationships.

0
source

More articles:


All Articles