Database Design Question (Need Suggestions)
Database Design Question (Need Suggestions)
(OP)
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
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
RE: Database Design Question (Need Suggestions)
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
RE: Database Design Question (Need Suggestions)
It looks like your requirements are a little different, but you might want to look at this thread:
Thread669-314333
Regards,
Ed