Employee Database Desing

[intifad]

Currently I am working on creation of database that stores information about employees in my company. The main problem which I have encountered is changing statuses of employees and tracking those changes in a way that will enable to report them later in easy manner. Let me give an example:
I have table Employees with team in which employee is currently working. I have also a table storing changes of teams where historical states of team assignation is contained. How can I improve that design?
Imagine that I want to prepare report in which for all months in current year number of employees will be evaluated in different teams. With current design it seems to me quite complicated, how may I improve that design or easily deliver such report with current design?


Database9.zip

[ItsMe]

What will likely happen with your situation is that you will come up against situations where you will have a Many to Many relationship (I cannot be certain about your business rules, this is just a guess). These can be difficult to wrap your head around.

At the very least, I see that timestamps are important to you. For instance, determining what date someone joined a tem and what date someone left a team.

I would start by trimming down how many attributes you have for your Employee tale. So, just the basic and most important info. Then, create other tables to store additional information that is related to the employees. If you start with something like the following for the Employees table, you will be forced to create additional tables that will Bridge to lists of relevant data.

tblEmployees
EmployeeID (PK)
FirstName (Text)
Surname (Text)
EmployeeNumber (Text) 'This is a business key issued by the HR department

With that, you can think of other entities ...
tblTeams
TeamID (PK)
TeamDescription (Text)


Then you can start to bridge your entities using an additional table
tblTeamHistory
TeamHistoryID (PK)
EmployeeID (FK)
TeamID (FK)
TeamJoinDate (Date/Time)
TeamExitDate (Date/Time)

This is just a vague way of describing the direction you will likely need to take. But, I would start there.