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.
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.
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.
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.
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.
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.
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?
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.
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.