SQL Server 2005 database design - many-to-many relationships with hierarchy

Based on the updated post, and making some (fairly obvious) assumptions based on the names used, I come up with the following. There are four entities:

• Divisions
• Departments
• Functions
• Entities

There are many relationships between these entities. Few of them are hierarchical, most are simple associations:

• Option A1: There is a master list of functions. Every department can perform (or do) one or more function, and a function might be performed by more than on department.

• Option A2: Functions are “owned” by departments. No function can be performed by two or more departments. (This appears to be the case, as the HR Dept has Payroll and Hiring, and the Finance Dept has Audit, Tax, and Accounts.)

• Functions are performed by departments for (on behalf of) divisions. (HR Dept does Payroll and Hiring for both Textile and Marketing divisions; Finance Dept does Audit and Tax--but not Accounts--for Textile division, and Audit and Accounts--but not Tax--for Marketing division.) Perhaps a bit more precisely, departments perform selected functions for selected divisions that they are associated with, and that association is defined by their performance of that function.

• Beyond performing the work of functions, there appears to be no relationship between departments and divisions. There is no hierarchical relationship between them, as one does not “own” or contain the other.

This leads to these roughly sketched out tables:

--  Division  -----
DivisionId  (primary key)

--  Department  ---
DepartmentId  (primary key)

--  Function  -----  (assumes option A2)
FunctionId   (primary key)
DepartmentId (foreign key, references Department)

--  DivisionFunctions  ----
DivisionId  (First column of compound primary key)
FunctionId  (Second column of compound primary key)

(You could optionally include a surrogate key to uniquely identify each row, but DivisionId + FunctionId would work.)

There isn’t enough material here to fully describe how "employees" fit into the model. Given that employees do the work of functions: can an employee do the work of more than one function, or do they only do the one? Does an employee do the work of the function regardless of the division(s) it is being done for, or are they assigned to do the work for one or more divisions? Two obvious options here, though more complex variants are possible:

• Option B1: Employees do the work of one or more functions within departments, and perform that work for all divisions that require that function of that department.
• Option B2: Employees are assigned to perform a specific function for a specific division.

Given these, tables might look like:

--  Employee  -----  (assumes option B1)
EmployeeId    (primary key)
DepartmentId  (foreign key, references Department)

--  EmployeeFunction  -----  (assumes option B1)
EmployeeId  (First column of compound primary key)
FunctionId  (Second column of compound primary key)

... and thus all employees that can perform a function will perform it for all divisions requiring it. Or,

--  Employee  -----  (assumes option B2)
EmployeeId  (primary key)
DepartmentId  (foreign key, references Department)

--  EmployeeAssignment  -----  (assumes option B2)
EmployeeId  (foreign key, references Employee)
DivisionId  (first of two-column foreign key referencing DivisionFunctions)
FunctionId  (second of two-column foreign key referencing DivisionFunctions)

(Or, instead of DivisionId and FunctionId, include the optional surrogate key from DivisionFunctions.) ... and thus employees are assigned individually to functions to be performed by the department for a division.

But that still leaves a lot of “what if/when” questions: Do employees “belong to” departments? Can employees belong to (work for) multiple departments? Perhaps employees belong to divisions? Do you track what functions an employee can do, even if they are not currently doing it? Similarly, do you track what department an employee works for, even if they are currently “between functions”? If an employee can perform functions A and B, and a division requires both these functions, might an employee be assigned to only perform A and not B for that division?

There’s a more requirements research to be done here, but I’d like to think this is a good start.