Where to start.....

[andy_25]

Hi all,

I have created many (10s) of databases for commercial use in the past and have taken on another project where I work now (a school). I am completely stumped as to where to start on this project and was hoping someone could help.

The Project: Create a ICT Suite booking system for staff self booking of rooms.

Notes:
School open 39 weeks/year, 5days/week, 9booking periods/day, 6 bookable rooms.

Data items:
Staff_Code Subject_Code Year_Group Period
Day
Date
Problem:
There are probably more data items needed than the ones I have got above, but I have tried for the past 2 days to normalise them and cannot figure it out. I have tried all sorts of composite keys etc but cannot figure out how to identify the specific booking (day/period/room) and get data pulling through properly.

Was hoping someone objective could have a look at this for me please. Thanks.

 

[namliam]

Start by making the 0 normal form, find all things you want/need to store... From there normalize and get going

As for composit keys, I suggest using autonumbers instead.

 

[andy_25]

Hi namliam, thanks for your reply.

"Start by making the 0 normal form"
I am not sure what you mean by this?

"find all things you want/need to store"
My data items show all what I "need" to store, the problem is figuring out what i have to store in order to identify the individual bookings.

"As for composit keys, I suggest using autonumbers instead."
You mean you would rather have a table with an auto number primary key and have the data items to identify the booking as foreign keys?

 

[namliam]

I have tried for the past 2 days to normalise them and cannot figure it out. I have tried all sorts of composite keys etc but cannot figure out how to identify the specific booking (day/period/room) and get data pulling through properly.

"Start by making the 0 normal form"
I am not sure what you mean by this?

You seem to know normalizing... 0 normal form is basicaly writing down everything and anything on detail which you (might) want to store in your intended database.

You mean you would rather have a table with an auto number primary key

Generaly speaking composit keys are more of a headache than they are worth + the whole natural vs meaningless PK thing...
I am a meaningless kind of guy, thus tend to use meaningless keys...
Meaningfull keys (room numbers/names for example) have a tendancy to change every now and then, causing all kinds of headaches to update PK and Referenced FKs etc... Meaningless PKs will not have that problem

 

[andy_25]

You seem to know normalizing... 0 normal form is basicaly writing down everything and anything on detail which you (might) want to store in your intended database.

I tend to start from 2, haha, but yes your right I need to go back to basics with this. I thought it would be a simple project.

I will draw up some designs and maybe come back with something more specific. Or scrap the project.

 

[namliam]

Or scrap the project.

LOL Afraid of a challenge?

 

[andy_25]

LOL Afraid of a challenge?

Haha very afraid, it's getting so frustrating I may burn my house down lol.

 

[namliam]

Haha very afraid, it's getting so frustrating I may burn my house down lol.

I bombed the whole of amsterdam today !!!

 

[NotSoRandomOne]

...The Project: Create a ICT Suite booking system for staff self booking of rooms.

Notes:
School open 39 weeks/year, 5days/week, 9booking periods/day, 6 bookable rooms.

Data items:
Staff_Code Subject_Code Year_Group Period
Day
Date ...

Off the top, one table for the rooms (which will consist of 6 records). One table for room bookings. One table for staff, and one table for subjects. The room bookings table will contain a field for: the IDs of the room booked room, the staff id, the subject id (although maybe not really needed), the date booked, and the hour booked (possibly as a plain integer, depending upon your needs).

My first iteration would be to have a form where the booker puts in date and time, and hits a button to find which rooms are available at that time. (That is simply a list of the rooms that don't have anything in the database at the requested time.) Then let them choose one of those rooms, hit another button, and it puts a booking record in the booking table.

I would pay very little attention to the 39 weeks/yr, the 9 periods/day, and the 5 days a week. If they tried to schedule outside of these constraints, I'd put in a messagebox asking them if they really wanted to come in on Saturday, but would let them if they chose 'yes.' As soon as you lock down your design to within those constraints, you can be certain that somebody will want to break them. Also, I'd make it easy to add and remove rooms to/from the system, and to block off entire days/weeks, in case a room undergoes remodeling, or some other activity that will take it out of commission for awhile.

I don't know what ICT stands for, but I hope that the above is useful. It sure beats working on the project I need to be doing, so thanks for the diversion.

Best wishes,
David