Data Model: Parent, Child, Grandchild with Child Being Optional

Question

I posted the question in another forum and I was suggested to re-post it here which may be more appropriate. Thank you in advance.

My organization structure:
   Department -- has many sections ( and employees)
      Section -- has many employees
         Employee -- most of the employees belong to Section directly.

However, there are two departments which have no medium Sections and employees of these Sections report to Department Director directly.

I do not know what can be done to better reflect the business structure using an Entity Relationship model.

I learned of a similar posting which is still different than what I need.
  https://stackoverflow.com/questions/2530215/model-a-zero-or-one-to-many-relationship?rq=1

John

Answer 1

Ask yourself what is the difference between a Department and a Section? Are they just organizational objects that may contain either employees or other, smaller organizational objects? Do they have any other differences in terms of their relationships and attributes?

If the answer is no, then you might want to consider replacing both with a single organization table containing an unleveled hierarchy. This can be represented with an involuted (self-referencing) foreign key (i.e. part_of or reports_to etc.)

Of course, in a relational database, unleveled hierarchies can be awkward to work with. If you decide to go this route, you should do some research on hierarchical data management techniques, like visitation numbers or adjacency lists.

If you really need to keep Department and Section separate because they have markedly different definitions, then you will need to consider implementing a dummy or null Section for each Department to handle the situation where there is no analogue in reality. If you go this route, you could consider adding a flag or some other indicator to differentiate between real Sections and pro-forma Sections.