How to? Database for school academic system

[savedlema]

I'm not sure whether this is a good question for this forum but, I'm struggling to set up a database to use in my school academic system that is supposed to record all student's scores and then be able to prepare a report that shows students ranks.

The hardest part is, if I have many students and many courses/subjects, and students take 2 tests every semester, how do I set up db tables to record these in such a way that I can later use sql SELECT statements to figure out student's total scores as well as ranks?

I currently have tables for demographic,courses,classes & scores as seen in the attached photo

[rproffitt]

Why not reuse and upgrade what you did before at https://www.daniweb.com/programming/software-development/threads/439391/fetch-students-marks-in-database-and-say-who-is-1st-2nd-3rd

Can you reveal why you've been puttering about this for so long? Is this a new assignment?

[savedlema]

Hi rproffitt,
Thank you for followup on that thread! For that app I developed (the one for that thread) I had to create too many tables, for instance, I created a table for every school Subject that students take. Then I also created a class table (say Grade 1) then students grades, say Physics was kept in its Phyisisc table and also in the Grade 1 table (for report generation)

For this new project, I want to reduce number of tables and use a single page to contain all students' scores, see ER diagrams attached here

After data entry (marks recording), the table may look like the second attachment
As it can be seen, this table will keep scores/marks for the entire school. I then find it difficult to fetch the marks of a single student (sID) and also compare him with the TOTAL marks of other students in his class to tell his rank in class.

May be this is not a good database design?
Thanks.

[rproffitt]

There are tomes about table designs and there are even more folk to call a design good or bad. For me, I only balk when it doesn't work, breaks easily, is a security issue and other items.

I don't see a problem with 4 tables in a design. Being difficult to do a few reports or queries is fine when the design works. Revamping a working design just to make a few reports or queries easy seems to never pay off.

And with that, I'll share a cheat I used long ago. It's probably heresy in some academic circles but here goes. I was stumped on how to make a report on a multiple table database. So I cheated (?) and made a temporary table with what I needed then ran the report off the temp table which I dropped when done.

Question: Was there a way to do what I wanted without that temp table?
Answer: Yes. But it eluded me for more than a few days and there are schedules to keep and it had to get done.
Answer a decade later: Looking back I just needed more SQL foo. The system runs as I made it in 2005 so maybe it wasn't that bad a decision as an academic may have thought of it.