A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Table design for a booking system



 
 
Thread Tools Display Modes
  #1  
Old April 26th, 2004, 01:20 PM
Brian C
external usenet poster
 
Posts: n/a
Default Table design for a booking system

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 a

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

  #2  
Old April 26th, 2004, 10:14 PM
chris
external usenet poster
 
Posts: n/a
Default Table design for a booking system

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



-----Original Message-----
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 a

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

.

  #3  
Old April 27th, 2004, 03:11 AM
TC
external usenet poster
 
Posts: n/a
Default Table design for a booking system

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...cles/Q100139.A SP

HTH,
TC


"Brian C" wrote in message
...
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 a

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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.