Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Designing DB to represent an organisations hierarchy

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JHamill View Post
    Would this be the right way to approach this?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    5
    Thanks for the quick response r937.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •