Member Avatar for 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

  • 2 Contributors
  • 3 Replies
  • 1K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by rproffitt
Member Avatar for 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
skulidb.PNG
After data entry (marks recording), the table may look like the second attachment scores_entries.PNG
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.

Member Avatar for 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.

commented: Thanks again. I also used temp tables on the other system which I also dropped after report generation, still works! +2
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.