Table Design and Efficiency Help (2 Viewers)

[GaP42]

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.

 

[DaveMD]

...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...

...I want to be able to track productivity for employees for those stats, then run reports to see those stats by employee, shift, by position, by assignment, etc....All of the data would be entered by each shift supervisor...

These points by @GaP42 are very important for database design decisions for tracking the statistics. The OP indicated the desire to be able to report on stats by shift, by position, by assignment. If the stats are only recorded at a monthly granularity, this wouldn't lend itself to reporting by shift, position, and assignment. For example, you wouldn't be able to account for instances where the employee changed positions, shifts, or assignments in the middle of the month.

If the stats are transactional based (like number of widgets sold), you could have the design of your tblStats track the stats by date for each employee. This would allow for aggregating the stats at the end of the month and differentiating the stats for reporting purposes by those different groupings that you note (e.g., by shift/position) since you can correlate which shift/position the employee was in at the time the statistic was produced.

 

[mike60smart]

The OP needs to give a more detailed description of his process.
At the moment we are all just guessing what it is he needs.