mysql - Multiple relationships between two entities, is this good practice?

Question

I have the following typical scenario regarding an office and its staff:

• Each staff member belongs to one office
• Each office has only one manager (a staff member)

ER Model

As you can see, this results in the relationship being recorded twice, once for the foreign key in the office table to point to the manager, and also in the staff table pointing to the office that staff member works for.

I have looked into alternative ways of modelling this but am still a bit lost. Please could someone advice a suitable way of modelling this, or if my method is acceptable for the scenario.

Many thanks

Answer 1

It's not that "the relationship [is] recorded twice", but that you actually have two relationships between these tables — which is perfectly fine. My only concern is, can a manager belong to the same office that (s)he's the manager of? (And relatedly: is it really true that every staff member has an office and every office has a manager who is a staff member?) If so, you have a circular dependency: you can't set the manager's office until the office exists, but you can't set the office's manager until the manager exists. As long as one or the other field is nullable, you can work around this by application logic (INSERT one, then INSERT the other, then UPDATE the first one), but it's a bit ugly. But if those are the relationships that exist, then there's not much you can do about it.