Table structure, relationships, and normalization Assistance

[deejabram]

I am looking for assistance in setting up the structure and relationships for my database. I have a screenshot of the current relationships and all relevant available.

FIXED (“Look Up” DATA): (Meaning data that remains consistent and will rarely change)
Value Streams- 6 Different Value Streams
Work Areas – Approximately 30 different work areas
Buildings – Approx. 162 different buildings
Standards – Approx. 15 different Standards (All of which do not apply to all buildings)
Shift – 4 different measureable shifts
Supervisor
Assigned Employees

HIERARCHY:
Value Stream
Works Area
Building

FACTS:
Each Building will only have one set of Standards
Each Building will have up to 4 shifts with 4 different Supervisors
· Some Supervisors may have more than one Building
Each Assigned Employee will only have 1 Supervisor
· Each Supervisor will have multiple employees
Each Area will have multiple Buildings
· No Building will have more than one area
Each Value Stream will have multiple Areas
· No Area will have more than one Value Stream
Standards may apply to multiple buildings

My end goal is to have a repository where my area supervisors can enter their attendance which will determine their capability for the day based on the standards (standard*attendance*direct labor in = Capability) for the numerous standards which encompass their work areas.

I would like to record the four “Shifts” for each “Work Area” on one form and/or query so that the form can display all relevant information.

I attached a screenshot of my exsisting relationships if this makes your life any easier.

 

[deejabram]

Just for clarification: The "assigned employees" should be "Number of assigned employees". Any assistance that can be given would be apprceiciated more then you will know. Donations can possibly be considered depending on the level of assistance given.