Design questions regarding variably multi-value data

[nkneer]

I'm designing a database for my workplace, which is a special needs educational center. Right now, all of our records are in paper form in binders, but we'd like to switch to an electronic system for convenience. I've got a few specifications and am having a bit of trouble figuring out the best, most elegant, and futureproofed design.

We provide a pretty large variety of services, and we'd like to be able to keep track of the services received (and details of service such as start date, therapist name, staff:student ratio, schedule) over time. Ideally, we'd have all of the current service information easily accessible and relatable, but then also have archival data we can look at to see what services, ratios, etc they've had in the past.

What's the best way to keep detailed, variable-field-number archival data as it changes?

My primary questions have to do with how to best handle variations in student records. For example, some students receive more than one service from us (so, perhaps they attend our school program and they also use us for appointment-based speech therapy). We'd like to have the ability to keep track of what services they receive and the relevant details of that service.

To perhaps better illustrate this, I could do this in an extremely kludgey way by having a Service Information table set up like

Student Name
Service 1 - Type
Service 1 - Start Date
Service 1 - Session Start Time


Service 1 - Session End Time
Service 1 - Session day(s)
Service 1 - AM Lead Therapist
Service 1 - AM Ratio
Service 1 - PM Lead Therapist
Service 1 - PM Ratio
Service 1 - Appointment-based Lead Therapist
Service 1 - Appointment-based Ratio
Service 1 - Supervising Analyst
Service 1 - Funding Source
Service 2 - Type
Service 2 - Start Date
Service 2 - Session Start Time
Service 2 - Session End Time
Service 2 - Session day(s)
Service 2 - AM Lead Therapist
Service 2 - AM Ratio
Service 2 - PM Lead Therapist
Service 2 - PM Ratio
Service 2 - Appointment-based Lead Therapist
Service 2 - Appointment-based Ratio
Service 2 - Supervising Analyst
Service 2 - Funding Source
Service 3 - Type
Service 3 - ...etc
...etc

Obviously, this isn't the best method because it means that a lot of records will have empty fields for services 2, 3, 4, 5, 6, etc, and it also means that we have to guess how many services kids receive.

I'm also running into this problem with other records we'd like to have -- different students will have different numbers of persons who are authorized to pick them up (and we need to have a field for each pickup person's phone #s), different numbers of medications & dosages taken and/or administered at school (and some don't take any), etc.

What's the best way to manage variable situations like this elegantly and scale-ably?

[June7]

Review data 'normalization' principles. http://support.microsoft.com/kb/283878

You are describing many-to-many relationships. Each student can have many services and each service can be associated with multiple students. This requires a junction table. Consider:

tblStudents

tblServiceTypes

tblStudentsServices
StudentID (foreign key)
ServiceTypeID (foreign key)
StartDate
SessionStartTime
SessionEndTime
SessionDays
AMLeadTherapist
AMRatio
PMLeadTherapist
PMRatio
AppointmentBasedLeadTherapist
AppointmentBasedRatio
SupervisingAnalyst
FundingSource

Apply same concept to table for medications. You might need a table of the approved medications for a student and another table to record the administration of those medications.

Whether or not you have a related table for contact info is determined if you want to allow any number of contacts. If you want to limit to 2 max then have fields in tblStudents.

Avoid spaces and special characters/punctuation (underscore is exception) in naming fields and database objects.