1 Attachment(s)
Scheduling Tables and Relationships question
I am in the process of creating a scheduling application in vb.net, I am using MS Access as the backend.
Simple scenario:
- the boss puts a blank schedule on the desk
- staff select the days they want to work
- she then uses my app to assign staff to the days
- previews and verifies schedule
- 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:
- Start Date
- End Date
- Date Range
- Employee ID
- Days Selected
Current Tables and Relationships
Attachment 80482
Am I going about this the right way or does this need to be changed?
Re: Scheduling Tables and Relationships question
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.
Re: Scheduling Tables and Relationships question
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
Re: Scheduling Tables and Relationships question
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.
Re: Scheduling Tables and Relationships question
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.