I am creating the backend of a microservice that will serve as a tool to see in real time how the company's employees are distributed by projects and what days they have assigned to each one. The database I have raised looks like this:

The company has 4 departments (Development, Product, Data Science and Operations), but each department of course has its different positions. For example, in development there is Backend, Frontend, SRE and so on for the other departments. My question is if I should create another table for these positions (That is, between departments and employees), or if I should leave the position as an attribute. To give a little more context, the company is interested in knowing every day how many employees are assigned to projects according to their position, for example, if I have any free backend developer today. If more context is needed to say, please tell me.

CodePudding user response：

If you want to be able to model a future possible situation in which an employee is a member of two or more departments, then yes, you may place a bridge table between employee and department. But if that's not a realistic scenario that you see ever coming, it safeguards integrity better to force the one-to-many relationship the way you have it above. Also, I would point out that your model suggests a task can only be assigned to one employee. Are you sure that's what you want? You may need a bridge table between Tasks and Employees to allow a task to be worked on by more than one person.