Given the OP requirement indicated that this design is to monitor employee performance, the focus on the transactional aspects of the employee shifts, assignments and training may not be the approach you need (although an understanding of each is absolutely needed). Your real starting point should be a definitive list of the outputs needed from your system: what are the reports expected - what will they show, how are the numbers put together: eg what constitutes employee performance? number of shifts per month? number of widgets produced per shift by employee? or the value of the widgets produced per shift by employee? These "definitions" will tell you what data you will need. What are the facts that are presented and what are the dimensions by which the facts can be aggregated. Facts can only be aggregated if they are held at the appropriate dimensional level of detail
Then you will need to determine how you will source them - you indicated shift managers will enter them at the completion of each shift in the OP? Are you designing this as a system to capture the "facts" from the shift managers? Is this data available from other sources - HR/Payroll, Stock/Inventory, Training systems? If they are which data would you trust to give you reliable data? Can you obtain that data and process/load into your performance management system?
For the performance reporting the table structure/design should be a data mart - start with a star schema:
Fact table: EmployeeID, DateReported, ReportingPeriod, ShiftsCompleted, LeaveDays, HrsWorked, Measurementname1, measurementName2 etc
Dimensions
Employees: EmployeeID, Name etc (from HR)
Time: Date, Week, Month, Qtr, FiscalYr
Metric: MetricName, Target, Qualifiers (Shifts/ HrsWorked?) etc, Annual/, qtrly/monthly
Without a detailed analysis/review of the performance metrics required it is not possible to determine/elucidate a full structure.
Note: the Fact table is not normalised: it is specifically designed to support analysis through simple queries as opposed to a table structure that is normalised that attempts to optimise the maintenance of transactions or events.
Then you will need to determine how you will source them - you indicated shift managers will enter them at the completion of each shift in the OP? Are you designing this as a system to capture the "facts" from the shift managers? Is this data available from other sources - HR/Payroll, Stock/Inventory, Training systems? If they are which data would you trust to give you reliable data? Can you obtain that data and process/load into your performance management system?
For the performance reporting the table structure/design should be a data mart - start with a star schema:
Fact table: EmployeeID, DateReported, ReportingPeriod, ShiftsCompleted, LeaveDays, HrsWorked, Measurementname1, measurementName2 etc
Dimensions
Employees: EmployeeID, Name etc (from HR)
Time: Date, Week, Month, Qtr, FiscalYr
Metric: MetricName, Target, Qualifiers (Shifts/ HrsWorked?) etc, Annual/, qtrly/monthly
Without a detailed analysis/review of the performance metrics required it is not possible to determine/elucidate a full structure.
Note: the Fact table is not normalised: it is specifically designed to support analysis through simple queries as opposed to a table structure that is normalised that attempts to optimise the maintenance of transactions or events.
