representing doors in ER schema

Question

I have something like this in my ERD.

Note that there can be two rooms connected by more than 1 door. Translating this in tables I get that every couple door-room is repeated twice (One time as entry and another time as exit). So I simplified my ERD schema like [this]

Where room1 and room2 are foreign keys.

During lecture we never saw an '' ísolated '' (without relationships) entity but browsing here on Stack Overflow i saw someone saying that was possible and correct.

Is my solution above correct?

Answer 1

Yes, there can be isolated entities in a diagram.

But your corrected diagram is incomplete: it gives the impression that the entities are isolated whereas they are not; doors and rooms are related and the relationship should appear.

If you'd correct this relation, you'd have something similar to the first diagram, but instead of IN and OUT, you'd have BETWEEN 1 and BETWEEN 2.

You would then immediately notice that your design is not fully equivalent to the original one: the original design allows to represent unidirectional doors. This sounds strange, but if you go in large office buildings, you'll see for example that:

• some doors are emergency only doors: you can open it to access the emergency exit stairs, but in the stairs, you cannot go back.
• some doors have a handle on only one side to allow exit an office, but yopu have to enter via another one.

The original design requires many duplicate doors because of the unidirectional design (this facilitates graph exploration algorithm). You could still achieve the same expressiveness, by adding either two attributes from_1_to_2 and from_2_to_1 or a single attribute direction which could have three values (unidirectional from 1 to 2, unidirectional from 2 to 1, or bidirectional, the encoding is left as an exercise).

This revised design would be superior to the original design, since it would prevent redundancy, and facilitate normalization if other information about doors need to be recorded (e.g. material, type of lock, fire resistance, automatic closing or not, serial number, etc...).