Advice on database design-How am I doing so far? (1 Viewer)

[j_cocker]

I am designing a database for an after school club(s), that should be able to handle (amongst others):

- Keeping a record of the children
+ Personal details
+ Health details (allergies, special needs, etc)
+ Which school attending
+ Which after school centre attending
+Which sessions (e.g. Breakfast club on Monday at Centre1, and every weekday afternoon at Centre2)
+ Status (active, not active, etc)

- Keeping information about guardians (i.e. those allowed to pick up the children, could be parents, family, etc). They are also the emergency contacts.
+Many to many relationship with the children table

- Keeping information about who pays the bill for each child (typically one parent that pays for one or more child)

- Booking information for each child - i.e. all the sessions that the child (his/her parents) commit to (until further notice). These will be charged for regardless of attendance.
+ This is necessary for staff allocation between the centres to ensure capacity allowed and staff/child ratios are not breached.

- Register. On a per session basis, keep a log of all those physically attending a session (required by law apparently)

- Intention is to later add more functionality to handle employees as well.

The type of queries that I will require to perform later include:

- Show all guardians for a child
- Show balance for each payee (hence tblTransactions that will hold payment and charges due)
- Show or a per session and centre all the children booked to attend.
- etc...

The DB was designed on the basis of information obtained from a real such operation, so I am comfortable that I do the right thing. What I am not sure about is weather I do it right... I would really appreciate any comments from you knowledgeable lot regarding my database design. The tables and relationships between them are attached below.

This is my first ever engagement with MS Access (or DBs of any type), so this is my effort so far after two weeks of evening with a book and Google by my side. I do have quite extensive programming experience, not recently though, and can easily understand the various VBA code snippets that I have come across so far.

Does the project seem feasible for someone with my level of knowledge, or am I aiming too high with my first project?


Many thanks in advance,

J.

 

[stopher]

Can't see much wrong and a pretty good start given your experience. Just a couple of queries...

I don't understand why Sessions is related to Bookings and Register.

Is it really the Payee that is related to the child? Or is it in fact the transaction that is related to the child. What I'm saying is there is no way to at the moment to relate a transaction with a Child. Is that correct?

How are you going to record the rate for a booking? At the moment it's a lookup which is fine. But it is also restrictive - what if you needed to record a discounted rate for one particular booking (for whatever reason).

I think this is a good project to start and you seem to have a good handle on the relationship concepts.

hth
Chris

 

[j_cocker]

Can't see much wrong and a pretty good start given your experience. Just a couple of queries...

That's good to know. I was getting quite worried with the growing number of tables...

I don't understand why Sessions is related to Bookings and Register.

The Bookings table is meant to hold records for slots (i.e. sessions) booked for a child. So a parent/Payee can book a session (say Friday afternoon) for a child, either as a one-off or for a period (say a term). It also allows for monitoring the number of places in each session (number is limited).

Is it really the Payee that is related to the child? Or is it in fact the transaction that is related to the child. What I'm saying is there is no way to at the moment to relate a transaction with a Child. Is that correct?

Well spotted! I spent a lot of time on this one trying to get it right. You are, of course, correct that a transaction should be primarily linked to a child and not to the payee. The problem is that very often you have several siblings attending a centre together, and then the Payee (parent) simply pays the outstanding balance with a single transaction.

I was a little worried that it would be too complicated to monitor the balance that way. For example, sometimes they don't pay in full - so credit full to one child, and partial to the other? No sure but although it is not the most elegant solution it seemed a simpler one... What so you think?

How are you going to record the rate for a booking? At the moment it's a lookup which is fine. But it is also restrictive - what if you needed to record a discounted rate for one particular booking (for whatever reason).

Again, I spent a lot of time trying to get this right. The prices are fixed, and there are very few of them. My thinking was that, again, it would keep things simple and uniform. You will simply need to add a new rate to the rates table for those very very rare occasions.

I think this is a good project to start and you seem to have a good handle on the relationship concepts.

Good - I was getting quite worried about the scope of the thing.

Many thanks for taking the time so far, much appreciated!