School Database design help

[lpm]

FMP8A, WinXP

I have the beginnings of a school file which I’ve been wrestling with and which includes the following tables: Teachers, Classes, Enrollments, Students, and Attendance.

Teachers ----< Classes -----< Enrollments>-------Students

1) Not sure where to place the Attendance table. Should I relate Attendance to Enrollments (many-to-one)?

2) Should I have a separate table for grades or should grades be an attribute of Enrollments?

3) Finally, not sure if or how I can archive the records at the end of each school year or if there is a better method than archiving.

I spent yesterday and today searching and reading over the Forums topics relating to my questions and I have found only a few small pieces to the puzzle.

Could someone please point me to some .fp7 example files or other resources. All the ones that I’ve looked at are pre-FMP7 and while sometimes helpful, I wonder if the improvements in 7 & 8 will provide better methods.

Thank you for any advice you can give me. lpm

[Matt_]

I'm working on a solution which may very well help you. Unfortunately, it is very specific to the geographical area I'm in (as far as reports, etc.) If I get a chance tonight or tomorrow, I'll try and upload it for you. It is quite large, and it's still under construction, but some of the basic features

(Student Records, Enrolment, Marks, etc.) are completed, and should allow you to see how to piece together your own solution.

Cheers!

Matt

[lpm]

Thanks a million, Matt! That would be great! I'm really at a stand-still right now! Thanks again, lpm

[Matt_]

My solution is too big and clumsy to upload onto here, so I made a very scaled-back and simple version. It has all of the relationships setup, as well as some sample data so you can see how it interacts.

It should give you a good place to start and build from.

If you have any questions, give me a shout. I've learned a lot trying to build my solution and I'm always looking to learn more... hopefully I can help.

Matt

db1.zip

[lpm]

Hi Matt: Thank you! This is great! As I look over the graph, I’m wondering if all relationships are in fact many-to-many or if you simplified the graph (without relationship details) in order to post the file (under the Forum size requirements). I’m also wondering about the “edit relationship” dialog settings. Finally, do you have more information about how/where to enter/track/report grades for courses and what to do at the end of the year to archive records? This is very helpful. If the big file has more details, I’d love to see it – is there a way I can see it (or you can send it) without posting to the forum with its upload limits? Let me know if/how I can facilitate this, and I’ll be happy to report back to you my progress. Thanks again, lpm

[T-Square]

I don't know where you are with this (and I'm not where I can look at Matt's solution for his take on things), but here would be my take on this:

1) Attendance would be related to Enrollment, since attendance is an aspect of enrollment.

2) A single grade would be an attribute of an enrollment--although you might want to consider an Assignments module that would allow you to track performance on specific assignments/tests, with some calculation for a cumulative grade. This might be a little data intensive (X courses * Y students * Z assignments), but it could be nifty from the "Well the final test is done five minutes ago, I've entered the results, and now I can enter the final grades and go off for the break" perspective...

3) I would keep all years in a single db, with year and term designators for courses and some sort of method to track student status year to year. Could be as simple as an Active flag, but a more elaborate tracking might be useful for determining pre-requisites, graduation status, etc.?

David

[lpm]

Thank you T-Square, these are good ideas and I will fold them into my design. Thanks again, lpm