How to code organizational structure in hierarchy in SQL

Question

I am building a structural dimension in a data analysis software. This structure should reflect the Organizational Structure. The hierarchy is as follows:

Business Unit
Department
Department
Department (possibly more)

The departments are related in a parent/child-relationship. The data table has the following columns:

Business Unit
Business Unit (Label)
Department
Department Label
Parent Department
Parent Department Label

I just do not know how to set up this code to create the parent/child relationship while also grouping to the correct Business Unit.

I will also be using joins to link employees to the corresponding departments, which I would happily accept advice on how to do. Thanks :-)

Tag your question with the database you are using. Sample data would also help. — Gordon Linoff, May 21 '19 at 14:14
Please add a tag to indicate which database engine are you using, such as MySQL, Oracle, or Microsoft SQL Server. Thanks. — Bob Jarvis - Слава Україні, May 21 '19 at 14:14
Possible dup of https://stackoverflow.com/questions/32148496/sql-select-query-for-organization-tree-hierarchy (SQL Server). — Bob Jarvis - Слава Україні, May 21 '19 at 14:16
Possible dup of https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query (MySQL) — Bob Jarvis - Слава Україні, May 21 '19 at 14:17

score 0 · Answer 1 · answered May 21 '19 at 14:34

There are many ways to achieve this, what I would have done is :

BusinessUnit (PK, BULabel)
Department (PK, BusinessUnitPK,ParentDepartmentPK, DLabel)
Check(BusinessUnitPK is null xor ParentDepartmentPK is null) : integrity check so that a departement "inherits" the business unit
Employe(PK,DepartmentPK)

How to code organizational structure in hierarchy in SQL

1 Answers1