Database design question

Cowboy

Registered User.
Local time
Today, 02:58
Joined
Jul 23, 2010
Messages
53
I am trying to create a scheduling program to help out one of my coworkers and I'm having some trouble designing the db.

I have figured an employee table to assign a pkEmployeeID to keep track of who is putting in availability.

I have broken out tables for the dates(tblYear, tblMonth, tblDay, tblDate, tblTimes). The problem I have is that I need to have all employees show up in an excel sheet with an x for each time slot they have available. There are 29 time slots (30 min increments from 8:00AM through 21:30 plus an "After 21:30" slot).

How would I store the information? Should I have an extra table with a field for each time slot and one for employee ID? Basically 30 fields in the table and a row for each employee's entry? This seems like it would not be normalized.

Any thoughts on how to design this would be great.

Thanks.
 
The Employee table sounds right.
I have broken out tables for the dates(tblYear, tblMonth, tblDay, tblDate, tblTimes).

I cannot imagine any reason to do that.

Basically 30 fields in the table and a row for each employee's entry?

Of course there would also be a field for the date.

Since the number of time slots is fixed it might be OK like this. However it really depends how you need to query the data. If all you are doing is exporting to a spreadsheet then it might be alright.

However databses almost invariably suffer from feature creep with requests for more information to be extracted. This structure might come against you later.
 
This is a sort of a crazy concept, but here is why that structure you described with the 30 columns might be wrong.

Access is now and has always been designed as a "sparse" database. Its paradigms are built around sparse tables. The idea is that you don't populate 30 slots. Instead you populate only those records that need to be populated. Part of the populated record will tell you the slot where that record would have gone if you were not using a sparse structure. Then when seeking conflicts, you build a record with the same kind of slot locators and then see if another record already has the same locators. If so, you've found a conflict. If not, you might be OK.

Hope that made sense.
 
Thanks for the responses.

I guess the only reason I would need the 30 (or 31 I guess since I'd be using a date field) fields is because it needs to be dumped into an excel sheet with all, some, or none of the fields holding a single boolean value.

I think your post confused me doc_man. Some how I would need to view an employee's 29 time slots against all other employees' 29 time slots.

Thanks.
 
Basically Columns are expensive, rows are free.

You can build this vertially much easier than you can horizontally.

Emp PK
Date
TimeSlot
 
Some how I would need to view an employee's 29 time slots against all other employees' 29 time slots.

As I said earlier what you can get away with depends on how you want to query the data and this confirms you definitely should use the vertical structure.

You will have a table TimeSlots with TimeSlotID and fields for any general information relating to the slot.

The spreadsheet format conversion would be done with a crosstab query using the TimeSlot table to create the fields that become the columns in the spreadsheet.
 
I'll really toss in the monkey wrench now, Cowboy. Technically, Excel is sparse, too. If you have a blank spot on a cell, it is blank because there is no entry that would go there behind the scenes. You don't honestly think that a spreadsheet with literally hundreds of thousands of cells available fits into the size of a small file, do you? Populate an Excel workbook with a small table and see how big it is. Then see how many cells are displayed. Then go back and verify how big that file really is. The ONLY way that Excel COULD work is to be sparsely populated.

Some how I would need to view an employee's 29 time slots against all other employees' 29 time slots.

No. You are programming with your eyes, not your head. This is the comment I made to you in another thread about the Excel paradigm vs. the Access paradigm. You are still on the Excel side of the fence when you take that approach.

For scheduling inside the machine, you merely need to know if the entry you want to make has the same row/column selectors as an existing entry - or not. If a person is occupied, you put a schedule record in place for that person and the time and date. Then nobody can schedule the same time slot because it is now "blocked" by the appointment record you just added.

For detecting availability, you would say that a person is available if no other entry has the same row/column selectors. Then the only trick is to throw in constraints that occur when a person is not there for some reason. (Like maybe if their schedule stops earlier or later.) For instance, put in a block of time that occupies more than one slot. Which means either making multiple entries or including the duration of the entries. Look at the Outlook appointment calendar. (Before you ask... yes, it is sparse too.) You can select the size of the slots. You can name a start time and end time, and Outlook handles the conflict analysis for you. Same concept, just look for the entries that would somehow interfere with your desired entry.
 
Thanks for some more explanation.
So am I getting this properly, that if I made form with checkboxes for each time slot, I would only save the information for a checkbox that is selected? If a user selects 13 of the 29 checkboxes, how do I store which check boxes have been selected in a table?

Am I understanding that right? Rather than collect data about "empty" slots, just collect the data about when a person IS available and store that?

Sorry if I am still not understanding this properly.
 

Users who are viewing this thread

Back
Top Bottom