Scheduling Tables and Relationships question

[CoachBarker]

I am in the process of creating a scheduling application in vb.net, I am using MS Access as the backend.

Simple scenario:

1. the boss puts a blank schedule on the desk
2. staff select the days they want to work
3. she then uses my app to assign staff to the days
4. previews and verifies schedule
5. prints out in an excel format

Application layout: a Month Calendar is used to select the start date of a 28 date range. Saves the Start Date and the End Date to textboxes. The 28 day date range (just the day, Oct 1, 2010), 1 is saved as the text property of a checkbox control. She then selects the checkboxes of the days the staff wants to work.

Data that needs to be saved:

1. Start Date
2. End Date
3. Date Range
4. Employee ID
5. Days Selected

Current Tables and Relationships



Am I going about this the right way or does this need to be changed?

[MarMan]

Instead of storing all the Days in a single row I would store one per row; DayNumber (1-28). Omitting the row if the employee is not scheduled. If more than one employee is scheduled, then there will be that many rows. I don't know what tblDates is for. You can always calculate the date on the fly.

[techgnome]

I'd have an employee table... and a selecteddates table.... the employee table is self-evident, so I'll skip it, but the selecteddates table would have the emplID (int, FK to employee table) and a selectedDate (datetime) .... and that's pretty much it.

-tg

[CoachBarker]

MarMan : tblDates is for the range betewwn Start and End tblDays is the days the employee selected.

TechGnome: guess KISS works here huh, will give it a try.

[CoachBarker]

OK if I do it as techgnome suggested, I would have a selectedDate table;

tblSelectedDates
SelectedDateID
StaffID
StartDate
EndDate

then I can loop through the range of dates to set controls, and to populate a DGV to preview the schedules I only have to worry about filling the DGV by either StartDate to view all staff scheduled for that period or StaffID to see all schedules for that employee, right.