Forum Discussion
Relationship & Data modelling
Hello,
I have 3 tables.
Date table - All days from 01.01.1999 to 31.12.2099 are included here.
Employee table - Each employee is listed daily. DepartmentID, GroupID and TeamID are constant. However, the names can change due to reorganisations or the employees change departments. Therefore, each employee is listed daily.
Event Table - Each staff member who performs a specific event is listed here. The EventCount column lists the number of times a particular event has been executed by the staff member.
A staff member can appear here several times a day, as they can perform different events.
1. I would like to know the number of employees by organisation and date in the employee table.
2. I want to know the number of employees by organisation and date in the events table.
3. I want to know the number of events in the events table.
4. i want to know which events were executed by which employees and vice versa.
My problem is the relationships and whether I need to split tables.
Currently I can only create a 1:n relationship between the date table and the other tables.
I am grateful for any support.
| Date Table | ||||
| Day | Week | Month | Quarter | Year |
| Employee Table | |||||||||||||
| Day | Week | Month | Quarter | Year | EmployeeID | First Name | Last Name | DepartmentID | Department | GroupID | Group | TeamID | Team |
| Event Table | |||||||||||||||
| Day | Week | Month | Quarter | Year | EmployeeID | First Name | Last Name | DepartmentID | Department | GroupID | Group | TeamID | Team | Eventname | EventCount |
2 Replies
It is a bit mess to understand, but first of all regarding the structure I think you need
- For all tables: Merge day,month, year to create a date column in a format dd/mm/yyyy. and remove extra columns for all except date table.
- Employee Table (remove dayWeekMonthQuarter and keep date column created)
- Event Table (remove dayWeekMonthQuarter, everything related to employee except ID and keep date column created). That is Event should contain (Date, EmployeeID, Eventname and EventCount)
And now...Relate event table with employee by using EmployeeID and relate event table with Date table by using Date field
So you must have:
- Date (Date, year, month, quarter, week, day)
- Employee (Date, EmployeeID, First Name, Last Name, Dept.ID, Dep.GroupID, TeamID, Team)
- Event (Date, EmployeeID,Eventname,EventCount)
You could also create an auxiliary table for a better performance Team with TeamID, Team but is up to you.
Once you have structured the data it will be easier to compute all you need
Hello. Thank you for your suggestions. My main problem is that I don't have a clear staff and organisation table. In my "employees" table I have:
1.) Daily every employee
2.) and the corresponding organisationThis means that all ID's are always present several times
I can't build a unique organisation table because at different times the organisation names keep changing.
