Thread: Need help on table designing

1. Need help on table designing

I am currently out of idea how to design this table so I would really like some suggestions. Description as follows:

Table will hold 3 exam result.
Exam A: 8 mandatory subject with infinite optional subject.
Exam B: 6 mandatory subject with infinite optional subject.
Exam C: 1 mandatory subject with 4 optional subject.

Feature to keep in mind:
1. Each subject's result need to be searchable (eg: Find A for Math in Exam A)
2. Basic total calculation (eg: calculate how many As in Math for Exam A)

Just inserting data I would be able to think of something however when putting the features into the mix, it just won't work.

My last resort is having a single table with: studentid, exam, subjectcode, result. This will work as in searchable and calculable however I have a feeling of a very messy and huge database in the long run.

My current design (given by my friend):
Each subject and its result have it's own field. It works but very hard to expand (add more subjects).

Any recommendations?

2. My last resort is having a single table with: studentid, exam, subjectcode, result.
I guess you'll have a students table as well (for all student info), and an exams table (containing the subjects and mandatory/optional for the exams), but the table that tells you the result by each student in each subject for each exam should look exactly like that, I think.

3. You can't do this in one table and still maintain 3rd Normal Form.

The results table should be something like:

Exam | Subject | Student | Result
------------------------------------
Ex. A | Math | Student 1 | A
Ex. A | English| Student 2 | B

Etc...

You'll need other tables to handle your other data though.

4. Originally Posted by transio
You can't do this in one table and still maintain 3rd Normal Form.
sure you can

the result table you gave ~is~ in 3NF

5. Originally Posted by r937
sure you can

the result table you gave ~is~ in 3NF
Right, but it's not a complete solution... it's ~only~ the results table...

You'll need more tables to model the other required data for the app he's specified.

6. and what other data would that be?

i looked through post #1 carefully and couldn't find any

7. Originally Posted by r937
and what other data would that be?

i looked through post #1 carefully and couldn't find any

How about # of mandatory / optional subjects per exam? You can't properly implement the business logic behind that without an exam table (minimum) that specs those figures.

And implied specification of which subjects are mandatory for each exam... (Feature #2 implies a desire to group on the subjects per exam... which should be able to left join for subjects that have no results listed)

I can think of 2-3 (minimum) additional tables required to properly model the above.

8. that's actually really hard to do with sql

i'd love to see your suggestion

9. Originally Posted by r937
that's actually really hard to do with sql

i'd love to see your suggestion

I'm not suggesting that the app be controlled by the DB, but the schema should account for the metadata required to model an app around it.

Subjects are unique for each exam hence having it's own table. The same goes for Grades. Another is of course, the student's table.

As for differentiation between mandatory and optional subject I believe it does not matter as much as I can just fixed it in the form itself. Being expandable is more important (as in they can add any amount of subject they wish and enter its grade).

I have thought of the same thing as transio and agreed by guido2004. However the one thing that make it 'last resort' is amount of queries needed just to insert all the results. I was told there could be a massive registration at a given moment hence having too many queries might not be a good idea. Especially since the average minimum subject a student takes is 8 on Exam A plus 10 on Exam B. That's 18 subject on top of all other queries.
Not to mention the calculation later on would be a resource nightmare. Oh just in case of a misunderstanding, the calculation is all grade for all subject for all exam at once and not one at a time.

FYI, I believe it should around 100 registration at a single moment. My server is a Xeon X3440 with 4GB of RAM.

11. Don't worry about # of inserts... the Results table should be insert-only (no updates), so you won't have to worry about performance... it'll handle pretty much whatever you throw at it (even 1,000s of inserts per second, sustained, with billions of records in total). Inserts perform very well in MySQL when you're not pegging the table with other operations.

To pull your results out, you can have a separate reporting DB that does nightly imports of the data from the live DB and "flattens" the data. This would also eliminate any unnecessary overhead on the live DB during hours of use.

12. Originally Posted by transio
I'm not suggesting that the app be controlled by the DB, but the schema should account for the metadata required to model an app around it.
i agree

so what is your suggestion for the schema to model the mandatory and optional bits?

13. Exams Table

Exam | Description | OptionalSubjectLimit
----------------------------------------
Exam A | Foo Bar Etc. | 4
Exam B | Foo Bar Etc. | NULL (infinite)

Subjects Table

Subject | OtherInfo
------------------------
Math | Foo Bar Etc.
English | Foo Bar Etc.
Science | Foo Bar Etc.

ExamRequredSubjects Table (for listing Mandatory subjects per exam)

Exam | Subject
-----------------
Exam A | Math
Exam A | English
Exam A | Science
Exam B | Math
Exam B | Science

ExamResults Table

Exam | Subject | Student | Result
------------------------------------
Ex. A | Math | Student 1 | A
Ex. A | English| Student 2 | B

14. nice job, steve

i like the required subjects table