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