Storing Live Details 3

[Gazzza]

Hi,
Can anybody suggest a method of storing large amounts of data using Access 2k table. I'm trying to replace an application that is using scheduing software to monitor room booking for 25 rooms that can be booked on the quater hour. I've built a table with 365 fields and store a key for what part of the day the table is booked. Then I'm planning to use it in other tables of the booking information. Everything will be approved manually. Am I on the right track here? Does Access have any easier ways of achieving this?

Thanks
Garry

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmm,

Don't go there?

Ms. Access (at least through ver '97) supports 'up to' 255 fields. And, this is generally considered to be generous. Modern? Relational? data base design favors "deep' as opposed to 'wide'. This is loosely translated to more records (deep) with fewer fields (Not Wide).

The 'moder' approach would be to include a record for each room/time slot which is "taken" (reserved). If you need a specific time slot, query the table to look for it. Also able to create the schedual for a specific room or look got the first available slot a particular room is avaialble.

At first glance, the wide (365 fields ... ) appears to be an attractive layout for the table. Later, it gets to be quite cumbersome in doing soe operations. The record per room per time slot ends up to be much easier to deal with.

Variations on the theme are easy to implement. The easiest (and probably the most attractive from the db design perspective) would be to have the reservation interval not be fixed to 15 min. This necessitates the inclusion of the end time, and implies some additional calculation for some activities, but - in general - would significantly reduce the number of records in the table.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

Most certainly: With Access you have to think a bit more "three dimensionally" compared to a spreadsheet. You may consider to build off of this idea: A table to store dates (your days)= 1 Field "TheDate", 1 field "DateID (autonumber). A table for your rooms: 1 field "RoomNo", 1 Field RoomID (autonumber). Hours: make a table with 1 field "Hours", 1 Field HourID (autonumber). Quarter hours:
make a table with 1 Field "Qhours" (i.e. Q1,Q2...), 1 Field QHrID (autonumber).
Now you need a table to connect what you've built. One room can have many days...Each day can have many hours, each hour can have many quarters. I'll leave you hanging here for a while as you absorb what you might need to do to continue. Write back as you go and we'll build it! Gord
ghubbell@total.net

 

[MichaelRed]

I would not go as far as Gord.

[Room | UserId | TimeStrt | TimeEnd] look like sufficient fields for the room reservation.

Other details of the app would determine additional fields / tables. Here, we are concerned ONLY with the room, and the time segments which are 'taken'.

The reason for the reservation, ammount charged, ... are all relevant - but not to the central issue of reserving the room.

In my experience, you will not have any single 15 minute reservations. You may schedule in 15 min, increments, but will not have single 15 minute blocks. Of course your use may be different, but 15 min is hardly sufficient to get in and out of a room, much less do anything. Thus to require that an entry be made for each such increment would appear to require more records be created than are necessary.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

Michael:
Your recommendation is swell however it creates a table that does not agree with good database normality design. If Garry were going to track the rooms you'd definitely require a table of rooms, otherwise entries could be erroneous. Time start and time end records become vague unless related to days somehow, and as the request is for quarter hour divisions a solid basis for them too. Take a look at the data that would result based on your design and decide if this is a potential for "oops sorry I didn't think any one was using this room WHAT the ... are you doing scenarios..!?"
Respectfully,
Gord
ghubbell@total.net

 

[MichaelRed]

Gord,

I do not disagree with the need for a [rooms] table, however it probably only needs to be a more-or-less static table. Depending on the whole app, in may need to include some of the attributes (capacity; size; rate/cost; furniture ...)

Since we know very little about the overal app, I chose to not dwell on the relations to other data structures.

As to the need to have a seperate table for each quarter hour increment, there I would disagre. Assuming the app is 'form driven', it would (in my opinion) be better to have the form (beforeUpdate?) function validate the start and end time entries - which SHOULD be full date-Time data type.

I have done a schedualing program in Ms. Access and found the structure described adequate for the use of business conference rooms. While our mininum interval was one hour, as opposed to the quarter hour. I do not think the difference is significant. The overall app included interdepartmental charges based on equipment usage, personnel required, catering activities and othet assorted attributes. While this was some time ago (some details are somewhat vague in memory), I do recall going through a number of desig approaches and deciding on NOT including a fixed table for the intervals, because of the excessive number of records which were/would be generated.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

Gerry:
There you go. Two different opinions on the same issue. It's now in your court to decide if 1/4 hour intervals or 1 hour intervals are suitable for your needs. Never forget, you can always pitch or archive old records...! Gord
ghubbell@total.net