table structure

[Guest]

As part of my employee scheduling database, I need to create an employee
availabilty table so I can keep track of what weekdays an employee is
available. I am not sure of the best way to do this. The way I have it
started is as follows:

tbl_employee
employeeID (autonumber PK)

tbl_weekdays
weekdayID (autonumber PK)
weekday (total of 7 records, 1 for each day of the week-mon,tues etc...)

tbl_availability
availabilityID
employeeID
weekdayID
available (yes/no field)

would I be better off scraping the tbl_weekday and creating 7 columns in the
tbl_availabilty ,1 column for each weekday with a yes/no field?

tbl_availability
availabilityID
employeeID
monday (yes/no)
tuesday (yes/no)
etc

which is the best route to go. 1 employee may be available for eveyday,
another may only be able to work monday and thursday. If the first table is
the best option, how do I set up a form with a checkbox for each day of the
week.

Thanks for any advice.

 

[Duane Hookom]

I would use the more normalized structure but without tbl_weekdays.
tbl_availability
================
availabilityID
employeeID
WeekDayNum (1-7 for day of week)

When you have a table of dates that need workers, part of your where clause
can be:
Weekday([WorkDate])=[WeekdayNum]
The above would be much more difficult if your table wasn't normalized.

A crosstab would get you the less normalized view of the data.