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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|