×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Database Design Question (Need Suggestions)

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

RE: Database Design Question (Need Suggestions)

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  

RE: Database Design Question (Need Suggestions)

Russ:

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

Thread669-314333

Regards,

Ed

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close