Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Staff with multiple roles in multiple branches

    Hello,

    my problem is probably rather simple and here it comes:
    Given multiple branches for a company, each branch has multiple staffs, However there is only one manager per branch, who is also part of the staff.

    How can I model this problem properly, so that it will be impossible to add another manager if there is one already and so that staff cannot be appointed to multiple branches?

    My initial idea was to have two entities: staff and branch. Then I would have had a foreign key such as manager_staff_id in my branch table and in staff I would have had a branch_id. This is a bad solution, since the manager may have a branch_id that is not consistent with what this branchobject has set as a manager.
    Well as all my ideas had these kind of flaws in it, I am asking for help now.

    Regards,
    Scummy

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Suppose you had a "BranchManagers" table. Obviously you'll want to add the constraints that the branch and manager are both keys. Now how would you enforce consistency between BranchManagers and the Staff table? Hint: The answer isn't in Domain-Key Normal Form.

    If this is something other than a homework assignment then it may help to specify what DBMS you are using.

  3. #3
    Join Date
    Oct 2008
    Posts
    4
    Well, having a BranchManager table sounds like a strange solution to me, since a Branch Manager has the same details as staff. If I wanted tables for each role, obviously, each of these table would have a staffid that it corresponds to. But in that case all my role tables had pretty much only this staffid in them. As there arent any depending attributes in these role tables, there would be no point in creating them, right?
    Can you elaborate on your suggestion? I do not quite get it yet.

    The DBMS is orcale.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You didn't mention any other roles before. This is why design-by-forum exercises such as this one are generally not very satisfactory. They invariably end up as a series of "what if this...? but what if that...?" exchanges.

    The purpose of the BranchManager table would record which staff member is a manager of which branch (instead of in the Branch table). It avoids what would otherwise be a circular reference between Branch and Staff.

    Since you are using Oracle the BranchManager table isn't totally necessary because Oracle supports deferred constraint checking, which makes it relatively easy to implement circular references. However, many people avoid deferred checking because not all systems support it and it requires additional code to ensure that the constraint isn't violated.

  5. #5
    Join Date
    Oct 2008
    Posts
    4
    Ok, I will try to get this thing straight:

    A branch has many staffs with certain roles employed. Some of these roles can only be appointed to a certain number of staffs.
    For example, there is always one and only one manager, there are at most there administrators(maybe less), but there are 0..n assistants in any one branch.
    All of these roles require storing the same information (first name, last name, adress etc.), which is why I do not want separate tables for them.

    I do not want to have ugly design in the database, therefore I would like to avoid the kind of constraints you mentioned. I am in fact looking for a straight forward solution, that is robust and hasn't got a lot of constraints attached, obviously I will probably need constraints for the number of positions available for each post. What I definately don't want is duplicate attributes, unless they will intentionally differ at some point, where this would have a particular associated meaning.

    The following things and only these will have to be considered in addition:
    staff might be replaced(e.g. one manager replaces another), staff might change position (e.g. assistant becomes administrator), staff might leave/be fired, the staffs salary might change and any one employee can only be registered at one branch. It is supposed to be possible to keep track of all these changes.

    So, this is the situation, but I am still unable to come up with a good solution to that. Any help is appreciated.

    Regards,
    Scummy

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    In my original post I did not mean to imply that non-key attributes such as names should be duplicated.

    In general I agree with your intention to avoid circular references and deferred constraints. So a table for each role is probably the way I would go. See: http://blogs.conchango.com/davidport...-Subtypes.aspx

    I don't know of any SQL DBMS that supports declarative N-cardinality constraints ("n assistants in any one branch"). Oracle and other industry-leading products all have extremely limited declarative constraint features - probably because of the inherent weaknesses of the SQL model for doing these things. I'm sure you will have to write some procedural code (triggers for example) to enforce all the rules you mentioned.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Can I ask...

    Is the manager of the branch just the member highest in the heirachy at that location?

    E.g.
    Terry - Corporate Dept - NULL
    George - ICT Dept - reports to Terry
    Steven - ICT Dept - reports to George
    Karen - ICT Dept - reports to George
    Andrew - ICT Dept - reports to Karen

    George is the "highest" member of the ICT dept, and therefore the dept manager?
    George
    Home | Blog

  8. #8
    Join Date
    Oct 2008
    Posts
    4
    The manager is highest in the hierarchy of branches.
    Assistants report to admins, admins reports to the branch manager.

    However, there is a company director, who manages all branches, and to whom branch managers will report to. The director is not associated to a particular branch though.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    So just to play devils advocate, you can easily work out the branch manager...
    As they are the employee in that branch whose manager is not in that branch.
    The company director is the employee without a manager.

    ...so is it necessary to store this information in another format?
    George
    Home | Blog

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Just my 2c but why not just use a simple structure to hold the data and then rely on a stored proc to add, delete and update the data. The stored proc will be the only method of altering the data and will enforce all the rules mentioned.

    Tables might be:
    • Branch: id, location details ...
    • Staff: id, manager_id, name, location details etc, from_date, to_date
    • Role: name, optional_branch_yn, unique_to_branch_yn, needs_manager_yn
    • StaffRole: staff_id, role_id, branch*, from_date, to_date


    The role table is the complicated one but allows the stored proc to decide if the staff member must have a boss, if there can be two branch managers in a branch or if the job requires a branch to be set. The procedure would decide what rules to apply by looking in this table.

    The from and to dates allow history to be kept so you could access old data if required. There are many ways of doing this but this is my usual method. Old records would have their to_date set to the current date and a new record would be inserted with the from date starting from this point.

    Data could be selected directly from the tables which wouldn't be difficult but you could add a view to make it easy to only view the current data for any user and role.

    This should cope with most of the issues you are having and should be reasonably easy to code and maintain.

    Mike

Posting Permissions

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