Table design for a booking system

[Brian C]

I'm trying to design some tables for a simple booking
system for school rooms. I'm having some problems with
trying to come up with a workable design.

In Excel I have created one record for each school day,
period and room (i.e. if there are 6 periods and 4 rooms
then I have 24 rows for each date) which I want to put in
an AVAILABILITY table keyed on Date, Period, Room. No
problem so far.

I want the user to search this table and if a room is
available then be able to book it. When the user books a
room I want to put the booking number in the AVAILABILITY
table. Thus the availability of the room can be determined
by the presence or abasence of a booking number in a field
in each AVAILABILITY record.

So I want a BOOKING table keyed on booking number which I
define as AUTONUM so a number is generated for me and link
it to the booking number field in the AVAILABILITY table.
The BOOKING Table contains data such as the teacher
initials, class name, etc.

If I enforce referential integrity then I need to have a
dummy record in the BOOKING table with a booking number of
one so I need to initially add all records to AVAILABILITY
with a booking number of one. So the check for
availability is the value of 1.

I also want the ability to block book so I have another
table BLOCK BOOKED also keyed on booking number. When a
teacher enters a block booking a flag is set in the
BOOKING table record and a record created in the BLOCK
BOOKED table that contains the start and end date of the
booking along with the day number. In the AVAILABILITY
table the booking number can thus be the same in multiple
records. Again I have to setup a dummy record in BLOCK
BOOKED to maintain referential integrity.

There is no link from BLOCK BOOKED to AVAILABILITY, this
is achieved via BOOKING.

My questions are:

How can I relate BOOKED and BLOCK BOOKED so that a record
does not have to exist in BLOCK BOOKED for every booking
but only if the block booking flag is set to Y? Can I
somehow use the combination of booking number and block
booking flag fields?

Is there another way of designing the tables, i.e. is it
usual to have 2 tables with the same key or should they
really be one table? Where do I stand in terms of
normalisation with this design by having 2 tables with the
same key?

Will this design create problems in designing my queries
to update the tables? I know very little about SQL at the
moment.

Thanks in advance.


So BLOCK BOOKED is 1:1 related to BOOKING and BOOKING is
1:n related to AVAILABILITY

 

[chris]

I would take this approach...

Have one lookup table for Rooms
RoomId
RoomDescn

Have one lookup table for Periods
PeriodID
PeriodDescn

Have one lookup table for the person making the booking
PersonID
PersonName

And one master table of Bookings
BookingId
RoomId
PeriodID
PersonID
DateBooked


I would not use a block booking table, or a table for
availability

By default, a room/period is available on a given date if
there is no entry for it in the bookings table.

Displaying availability should be a function of the
booking form, not an availability table.
The form would create a matrix of all rooms/periods for
the selected date, and then fill in the those cells that
are booked.

If you need to track cancellations as well, use a
cancellation table, and move the cancelled record from the
bookings table (which automatically frees the room) to the
cancellation table.

If you want to discuss this further, reply to this posting.
If you want help with implementation, reply directly to
chris
at
mercury-projects
dot
co
dot
nz

 

[TC]

Brian, a database product like Access is completely different to a
spreadsheet like Excel. They do not store data using the same ideas, at all.

To use a database effectively, you need to learn about database
"normalization". Normalization is not a concept that applies to
spreadsheets. But it is absolutely critical for databases.

Start here for a dry, but instructive read:
http://support.microsoft.com/support/kb/articles/Q100139.A SP

HTH,
TC