1

Given some specifications I need to design a database for an organization. One of the parts has to do with areas. In the organization there are different areas, each area has a boss, and there is also an employee as the boss of all areas. My question is about how I should indicate which employee is the boss of all areas.

    | employee_id |    name    |

    |      1      |    Mark    |

    |      2      |    Carol   |

    |      3      |    Adam    |

    |      4      |    Andy    |



    | area_id |    area_name    | manager |

    |    1    |    Chemistry    |    2    |

    |    2    |    Electronics  |    4    |

    |    3    |    Medicine     |    3    |

    |    4    |      ALL        |    1    |

enter image description here

At the moment what has occurred to me is to create an occurrence called "ALL" within the relation "AREA" to indicate the head of all areas, however I am not sure if it is the best way or the correct way to do it . Any other idea?

2
  • This kind of structure is usually hierarchical. Companies have multiple layers of departments & management. (Ex: Supervisor, Manager, director, VP...CEO. Or at a University: department chair, Dean, Chancellor,... President). Should your areas have a hierarchy? Or is it always flat, with one layer + the single "all areas" manager?
    – AMtwo
    Jan 2 '21 at 8:59
  • In this case it is flat, only distinguishing between the employees of the area, the area manager and the general manager. Jan 2 '21 at 17:20
1

It's ok to have an "ALL" value as an Area but it might be preferable and more flexible to add a third table, as a linking table, called EmployeeArea instead.

The EmployeeArea table is where you can store the area_id and employee_id (as manager_id), to represent the technically many-to-many relationship between the tables. Then the boss of all areas would have a record in there for each Area.

This is more normalized of a design, and is more flexible if for some reason there needs to be multiple bosses of all areas, or special cases where multiple employees are responsible for the same area.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.