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

Question

Note

I have completely re-written my original post to better explain the issue I am trying to understand. I have tried to generalise the problem as much as possible.

Also, my thanks to the original people who responded. Hopefully this post makes things a little clearer.

Context

In short, I am struggling to understand the best way to design a small scale database to handle (what I perceive to be) multiple many-to-many relationships.

Imagine the following scenario for a company organisational structure:

             Textile Division                    Marketing Division
                    |                                     |
          ----------------------               ----------------------
          |                    |               |                    |
       HR Dept           Finance Dept        HR Dept           Finance Dept
          |                    |               |                    |
      ----------          ----------       ----------           ---------
     |          |         |        |       |        |           |       |
  Payroll     Hiring    Audit     Tax   Payroll   Hiring      Audit  Accounts
     |          |         |        |       |        |           |       |
    Emps      Emps       Emps     Emps    Emps     Emps        Emps    Emps    

NB: Emps denotes a list of employess that work in that area

When I first started with this issue I made four separate tables:

1. Divisions -> Textile, Marketing (PK = DivisionID)
2. Departments -> HR, Finance (PK = DeptID)
3. Functions -> Payroll, Hiring, Audit, Tax, Accounts (PK = FunctionID)
4. Employees -> List of all Employees (PK = EmployeeID)

The problem as I see it is that there are multiple many-to-many relationships i.e. many departments have many divisions and many functions have many departments.

Question

Giving the database structure above, suppose I wanted to do the following:

• Get all employees who work in the Payroll function of the Marketing Division

To do this I need to be able to differentiate between the two Payroll departments but I am not sure how this can be done?

I understand that I could build a 'Link / Junction' table between Departments and Functions so that I can retrieve which Functions are in which Departments. However, I would still need to differentiate the Division they belong to.

Research Effort

As you can see I am an abecedarian when it comes to database deisgn. I have spent the last two days resaerching this issue, traversing nested set models, adjacency models, reading that this issue is known not to be NP complete etc. I am sure there is a simple solution?

Answer 1

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.

Answer 2

Well you wouldn't put it all into one table. You need to read up on normalizing data and joins. (And never store anything in a comma delimted list.)

No database worth it's salt would have the slightest problem handling a million records, that is a tiny database.

You need tables for functions, courses, locations, people, organization and possibly some joining tables to accommodate many to many relationships. But none of this is hard or even beyond very basic design. I recommend that before you do anything, you get a book on your chosen database and read up on the basics.