SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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. #3
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    and what other data would that be?

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    that's actually really hard to do with sql

    i'd love to see your suggestion

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  10. #10
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let me add a few more information. My apologies if earlier information was insufficient.

    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. #11
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    nice job, steve

    i like the required subjects table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •