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