Hi guys,

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;
Employee
Manager
System Administrator

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

Would this be the right way to approach this?

Thanks.

yes

it's called the adjacency list model, and it is characterized by the use of a "parentid" column (in your case, managerid)

by the way, that tomjewett article is really good

a similar hierarchical structure is Categories and Subcategories

Thanks for the quick response r937.

The link you provided is extremely useful - so useful in fact I shall be referencing it.