Database Design Question (Need Suggestions)

[RussOSU]

I am creating a Database with the following requirements for data to be tracked and stored.

Employee
Name
Address
Age
phone
training
certification
status

Certifictation
Type
Level

Training
Date
Type
Instructor

Instructor
Name
Type

Work
Dates working
Missed (yes/no)
Makeup days
# of Employees needed per day

Those are the required data needed. The problem I am running into is how to setup the work table. Should I set up a table for the dates with how many employees are needed. Then connect that to a work table that has employee id and date id and where they have missed the day or not? All of the main rules I am wanting to add I will be adding with VBA rather then complicate the tables anymore. I am looking for any suggestions on setting up the initial tables and relationships.

Thanks
Russ

 

[MattReed]

A little more information is needed for me to make an intelligent suggestion. What you are trying to do is set up a schedule and then track attendance. I am assuming that you care nothing about tracking hours worked, because you do not mention hours in your work table. What I would do is set up a Schedule and a Work table. They would be related much like an Order and Order Details table would be related. The Schedule Table would act as a sort of Header table that hold general data about each day worked for all employees as a whole. This is where the additional information is needed. You will need to have records in the Schedule table for each unique instance of work day. If your situation allows you to have the same schedule for each week,for each employee, then you would only need a schedule record for each day of the week.

Schedule
ScheduleID
DayOfWeek
NumberOfEmployees

Work
EmployeeID
ScheduleID

If an employee comes in to make up hours, then you would leave ScheduleID empty. This would allow you to track make ups.

Now, Other needs will force different tables and fields in the Schedule and Work tables. Let's say each emploee has a different schedule, but they stay the same every week.

MainSchedule
ScheduleID
DayOfWeek
NumberOfEmployees

Work
WorkID
ScheduleID

EmployeeSchedule
EmployeeScheduleID
ScheduleID
EmployeeID


The Main Schedule would hold all days in the week that an employee could possibly work. EmployeeSchedule would hold each employees scheduled days for the week.
Using SQL, you can then query these three tables to come up with lists of makeups, missed days, etc.

This case would be further complicated by other things, like swing shifts, Holidays, Etc. To take care of this, you would have to have a record in the MainSchedule Table for each unique day. You would also have to do that in the EmployeeSchedule Table. Schedules would have to be entered in each week, for every day and for every employee. You would also have to change the DayOfWeek Field to Date.

If you want to complicate things, try coming up with an algorithm in VBA that will automatically create a schedule based on Employee Needs and Company Needs. I tried to code for something like that, but I could not find an approach that I liked. I was trying to do it for a Convenience Store, where flexibility is key. You'll have enough fun designing the front-end for what you described in your question.

I'm hope that this helps. Good Luck!

Matt Reed

 

[olded]

Russ:

It looks like your requirements are a little different, but you might want to look at this thread:

thread669-314333

Regards,

Ed