Table design and relationships

[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
Attachment 1958

Is this the correct approach to what I am trying to do?

[ajetrumpet]

hello coach,

IMO, you should not have a storage device for 'selected days' at all. This is calculated information that needs to be created dynamically whenever it is needed. I'm not sure what your 'dates' table is for either, because the start date and end date storage should be all you need. And again, those two dates can serve as the criteria for getting the 'days' on the fly with dynamic queries or form calcuations.

In general, for any application, there really should never be more than 10-20 fields in a table especially for small companies and recreational use. Large companies though, can justify having more because more information is present in the everyday operations. Databases that usually sport this kind of format are not making use of the relational structure of the database, but rather using the program as if it were Excel.