Schema help for Studio booking app

Rails
1

Hi, I’ve offered to make a web based studio booking system for the
university I attended to replace their old pen and paper approach and
I’m having some trouble deciding on the best way to organise the
database.

What will happen is the students (or tutors) will log in and be
presented with a weekly calendar view of the studios, along with details
of existing bookings. They can then enter details of when they want to
book and which studio they will be using etc…

Each student has a certain amount of hours available for studio use per
module code. For example they may be allowed 100 hours for a recording
module using the code ‘REC’ and 150 hours for extra curricular study
using the code ‘EXTRA’. They will not be able to exceed this limit. Any
time booked using the ‘EXTRA’ code can be overbooked by other students
using a module code such as ‘REC’.

There are three years of study on the course. The second and third years
have unrestricted access to the studios but the first year students are
only allowed in certain studios.

So I figure that the details I will have to store in the database are:

PER STUDENT
Name
Password
Year of study
Which module/extra-time codes are available for use
Amount of hours used per code
Details of existing bookings

GENERAL
Which studios are available for use to each year
Total mount of hours allowed per code
Module/extra-time code precedence

What would be the best way to organise all of this information in a
database? I would really appreciate any help you can give to me as I’m
still quite new to programming and could really do with some experienced
advice. I can provide more details if needed.

Thanks in advance!
Henry

2

Anyone have any suggestions?

Please help me out guys!

3

Henry B. wrote:

Anyone have any suggestions?

Please help me out guys!

Use an Entity relationship diagram. Basically think of database tables
as entities i.e. a Student for example would be a table as would a
Studio. If a student could reserve time in a studio then you might make
Booking a table which would hold the details of each booking. You might
also have a table that describes the rules regarding each booking in
relation to the student, maybe Booking Codes as you mentioned above.
Once you have your entities then construct the relationships i.e. a
Student can make more than one Booking , but each Booking relates to one
Studio and so on. Test your design with some data and do some analysis
to prove that the model can support your requirements i.e. in relation
to the checking hours used are not exceeded by the students in relation
to the booking codes etc. Always look for weaknesses i.e. Shouldn’t end
up with a situation where the same Studio is double-booked etc.

4

Thanks for that, I had a think about it and came up with this:

http://www.henrybourne.co.uk/stuff/schema1.jpg

I’m not exactly sure if I should have any join tables in there or if I
have the relationships right… Does this seem like it would work?
Anyone have any suggestions to fix it or improve it?

Many thanks.