Designing DB to represent an organisations hierarchy
I am currently working on a web-development project where I am required to develop a database that fits the following requirements at this stage:
1) The database represents the companies employee hierarchy. For instance, it will be possible to view which employees report to which manager.
2) Users of the system (company employees) can be any one of three roles;
I intend to have a user table, where all system users are stored. A seperate table named 'role' with 'roleID' as the PK and a second attribute of 'roleDescription'.
Each instance of 'user' will have a roleID.
To meet the requirements of representing the company hierarchy I intend to include an attribute in the 'user' table of 'managerID' which refers to the 'userID' from the same table - with those users that are managers having NULL for the 'managerID'. Much the same they have described in this example: Database Design - Recursive associations