Need feedback about database design decision (Access 2007)

[Jenaccess]

Hi,

I'm working on an educational program database using Access 2007.

I'm currently designing Version 2. I made Version 1 when I didn't know much about database structure, and it's currently in production. A problem I'm having with Version 1 is that students have multiple entry and exit dates, sometimes five or more. The queries can be a nightmare, especially if an end user does not exit a student who is no longer with the program. Trying to find who is currently in the program at any given moment is next to impossible, and that renders the entire database kind of useless.

In Version 2, I want to allow only one entry and exit date. I want to capture students' time in the program by whether or not they have services (recorded in the service table) for a given date range. This seems to be a much cleaner solution. Is there any reason you can think of that I shouldn't do it this way, something I'm overlooking structurally? Or do you think it sounds like a good idea? I'm looking for any type of feedback, whether positive or negative. Thank you.

 

[Galaxiom]

Definitely the way to go. Your original structure technically breaches normalization principles since it is possible fo a student to still be recorded as participating in the program while they are not engaging any services.

 

[Lightwave]

Ok if I understand it at the moment you have quite a flat structure. Whereby entry and exit dates are stored against a student in a flat file.

If I'm getting this right you could have the header record as the student and then child records of "services" such that any student can have from one to an infinite number of services. For each service there will be a start and an end date.

Should be easy to define a query such that valid students are only those students for whom all their service end dates are beyond a stated date.

So a simple structure could be

T001StudentNames
PKID (primary key identification)
StudentFirstName
StudentSecondName
Dateofbirth

T002ServiceName
PKID (primary key identification)
Service

T003StudentServiceAllocations
PKID (primary key identification)
ServicePKID
StudentPKID
StartDate
EndDate

Table 2 is only included as it could be a look up for Table 3 which would allow you to add and subtract what I presume are standard set of services that occasionally contract and expand. T001 is the parent table and T003 is the child such that the PKID of table 1 is linked to the studentPKID field..

In such a structure...

Current students would be anyone accessing services for whom End date in T003 is greater than or equal to the date in question.

 

[Lightwave]

Point of note in the above set up T003 is acting as a junction table between T001 and T002 such that many services are connected to many students. This has the advantage that you will easily be able to see what students are doing an individual service as well as seeing what services an individual student is doing. (although that's not something you asked to be done!)

 

[Jenaccess]

Dear Galaxiom and Lightwave,

Thank you so much for your feedback. I'm glad that my original idea was on the right track. Thanks to John Big Booty on this forum, I now know how to base forms on junction tables. I could never get junction tables to work right in the past, so I just didn't use them, which was detrimental to Version 1 of this database.

Could someone please tell me what "flat file" means? I've been hearing this expression a lot, but I'm not quite sure if it just means the database is designed too much like a spreadsheet, or something else?

Lightwave, I'm so grateful for the normailized structure you showed me. Right now my structure looks like:

tblService
ServiceID (pk)
ServiceName
ServiceCategory

tblStudent
StudentID (pk)
LastName
FirstName
DOB

tblStudentService
StudentServiceID (pk)
StudentID (fk)
ServiceID (fk)
ServiceDate
ServiceTime (in hours)
Program

Student is the parent table; Service is the child table and StudentService is the junction table. The student form is the main one, and the subform is based on a query with the Service and Student Service tables. Though I'm only working with one overall program, there are three subprograms that I'm trying to keep straight. That's why I added the field Program to the StudentServiceTable, as a way to give the program sponsoring the service the proper credit. Do you think this is a good way to do it?

I only have service date. I never thought to have a beginning and end date for a service. That's a good idea and one I'll add it to my structure.

The only problem I have with doing this by service instead of entry/exit date is some of our people who do data entry don't have constant access to the database. It is located on a network server, and they are located offsite. So, unfortunately, the database is not updated as much as I'd like. I recently discovered that Access 2007 has a Manage Replies feature. Do you think this would be a good way to collect service data from offsite people? Do you know of any better way? Thank you so much for your help. Believe me, it's appreciated.

 

[Lightwave]

Dear Galaxiom and Lightwave,
Could someone please tell me what "flat file" means? I've been hearing this expression a lot, but I'm not quite sure if it just means the database is designed too much like a spreadsheet, or something else?

Yes "flat" relates to the structure of data within a database. Any database containing tables not related to each other I would describe as flat. Any parent child relationship breaks that flat structure and makes the structure relational.

Therefore although Access is described as a relational database it can contain information that is flat. Nearly every excel sheet with information in a table would be regarded as flat if that data was on a single worksheet.

Right now my structure looks like..

Looks like exactly the same structure as I described so given the information you have described yes I think it is a good way to do it. There is not always a right and a wrong with these things.

Regards your program idea you could if you wanted move the program to a separate table like the service item and just include a programID within the junction table. The main reason this would be done is if either the names of the programs were being constantly expanded decreased or renamed as more experienced designers would then be able to create an editable form that would act as a source for the field. From what you indicate the implication is that there are only 3 programs and these do not change a lot so this might be overkill. Either way the junction table is now allowing a many to many relationship between Students , Services and Programs. You could add any further numbers of attributes that are unique to the student (header record) and you would expand the many to many relationships accordingly.

I recently discovered that Access 2007 has a Manage Replies feature. Do you think this would be a good way to collect service data from offsite people? Do you know of any better way? Thank you so much for your help. Believe me, it's appreciated.

I don't have A07 I use access 2003 so I have no experience of this. I would suggest though that it very much depends on things like, the regularity of update, number of alterations and what the summary information is used for. I suspect it is management information prepared periodically.

I find that management information rarely needs to be "live". Provided it is accurate once complete a couple of days delay tends to be fine an exception to this might be stock levels for example which are more operational in nature. Often if its a couple of days old as it is maybe periodically prepared ie monthly or yearly that's fine and no point stressing out doing something incredibly complicated not that the analysis might not be very useful.

Thus paper sheets might be a really simple solution.
They're cheap everyone knows how they work and don't need batteries

PS I'd made a mistake in my description of the tables in my previous post to this which I have now corrected.

 

[Jenaccess]

With your help, I think I have a good handle on this. Thank you so much for your time!