Creche,, nursery and Event Management

[Carlo]

Hi

We run a community centre which provides training, rooms for events, a
nursery and a creche.

I currently have a database set up as follows:

Child Users - Child User ID
Child Nursery Users - Child User ID
Adult Users - Adult User ID
Adult Nursery Users - Adult User ID
Custodianship - Child USer ID and Adult User ID

This is enough for me to monitr the users of our centre. I now need to set
up a booking system, so that I can book children into the nursery (no more
than 35 at one time), book children ino the creche (no more than 8 at one
time), book adults onto courses (variable limits) and then record their
attendance, and book out our rooms (together with catering and equipment) for
external events.

My main problem is placing limits on the number of people attending at a
given time. Though general suggestions on the structure would be very very
welcome.

Thanks a lot

Carlo

 

[Clifford Bass]

Hi Carlo,

It is a start. One thing to do is figure out in plain language all of
the information you will need to record. You will also need to figure out
how it all fits together, both from the users functional needs (i.e. what
they do and how they do things) and in the database. Then make the step to
figuring out what tables are needed and what fields go in them. From what
you mention, you are going to set up a number of other tables such as ones to
define the nursery, creche, course information, equipment and so on,
including limits. An you are going to need several more table to hold
registration, session, equipment, catering and other information. When
adding people to courses, the nursery, and so forth, you will need to check
how many are already scheduled for a particular session against the limits.
Or for equipment, if any is available for the session. You've got a big task
ahead of you. So, again, if you have not already done so, you need to do a
thorough analysis of the whole process before getting to the design details.

Hope this helps,

Clifford Bass

 

[Carlo]

Hi Clifford

I've already identified what I hope is all of the information that we need
to record, and have tentatively set up the required tables, but am having
some trouble visualising it and the relationships, so was wondering whether
anyone ese had den somethign similar an had a workable set up. In particular,
I dont know how I would avoid over-filling the creche and nursery, and how I
would avoid double bookings... do i need a separate table containing dates,
and then link that to the sessions?

 

[Carlo]

Also, a related question, is how do you deal with recurring events? Is there
a way of easily booking 10 at once, if all of the specifications are the same?

 

[Clifford Bass]

Hi Carlo,

What you are describing has indeed been done before. Maybe someone
else does. Essentially you are doing room/event/course/appointment
scheduling. I do not have this myself, only have worked with them in other
systems.

To avoid overbooking or double-booking say the nursery, you will need
at a minimum a table that holds the occupants of the nursery. It might have
a person ID, linked to the persons table, a planned check-in date/time, a
planned check-out date/time, an actual check-in time and an actual check-out
time. Then when someone wants to add another person to the nursery for a
certain timeframe you would need to get a count of all of the people who are
actually there during that timeframe plus those expected to be there. If it
is at the maximum number, then no more allowed in. The nursery is the easier
one because there are do not need to be any "sessions". And when booking
someone, you can check to see if they are already booked for any part of the
requested new time frame.

For the courses kind of thing you will need to define all of the
available sessions in one or more tables that are children to the courses
table. So you might have a sessions table with a session ID, description,
start date and end date, and maybe a start time and end time. Then you will
need a session-participants that holds the people attending the sessions
(session ID, person ID). Then when someone is going to be added to a
particular session, you can check how many are already scheduled. If you
make the session ID and person ID the primary key, then you will not be able
to book the same person more than once.

Essentially, for any new entry, you are going to have to do a fair
amount of processing to determine if it can go ahead and be accepted or if it
has to be rejected.

As to recurring events, one way to do that might be this. Have on your
main form that adds a session a field that specifies how many occurances.
Also have a button that instigates the booking process. That process will
simply loop the specified number of times indicated, adding to succeeding
events.

All this is much simplified. Hope it helps.

Clifford Bass