How to design a database where employee can belong to either department or division

Question

Hi there,

I have a database where employees, departments and divisions are stored. Each employee can belong to either a division or a department (1-to-1 relationship). To store that data I have created three tables: Employees, Departments, Divisions. How do I correctly wire them to store such relationship.

The easiest way is to have in two nullable foreign keys in Employees table (department_id and division_id) but I was wondering if there is a better way to do it?

Answer 1

Not necessarily better, but an alternative would be to model Departments and Divisions as subtypes of a more general entity "Business Unit", and then link employees to Business Units.

Answer 2

Alternative answer:

If you're stuck with the two tables for divisions and departments, assign employees to a type of business unit and reference with an id.

Add a table called 'assignment_type' with two records (today): division, department.

Add a table called 'employee_assignment' with three columns: employee_id (fk), d_id (fk), assignment_type (fk).

Answer 3

Since you are dealing with employees and divisions, I guessing that you are not bulk loading lots of rows of this data all the time. Based on that assumption, retrieval is what is important.

Since there are several ways to store this data, you need to consider what types of queries you will need to retrieve this data. What types of searches, reports, or data loads will you have to implement. Based on that, you should look at each way to store the data and how the performance and ease of writing the needed queries will be. How can you add indexes, etc. The coolest way to store the data may end up being a major pain to query back the way you need to.

Answer 4

Tony's Business Unit approach is one way to go, especially if the set {Devision,Department} might grow in the future.

An alternative is to go with the two foreign keys approach, but include a check constraint such that one and only one of the columns in the Employee table is non-NULL.

You can go down further routes - e.g. if the domains of the Primary Keys of the Department and Devision tables are disjoint but compatible, you can create a single (set of) columns in the Employees table which (depending on their values) are a reference to either a department or a devision, and then use computed columns as the source of the foreign key constraints.

All are doable, and you have to go with what you find most natural.