SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Design Question (Not sure how to design this part)

    I'm not sure how to design this part of my database but I can tell you how it will be on the front end part. Can someone help me with this?

    The front end is going to look like this:

    Code:
    Testing Information For student A
    | Time |  Test 1  |  Test 2  |  Test 3 |
    | 8:00 |    3     |    5     |    2    |
    | 4:00 |    7     |    2     |    3    |
    | 2:00 |    4     |    5     |    1    |
    | 9:00 |    7     |    0     |    1    |
    Depending which student is selected, the amount of tests can change, so student B could have Test 1-5 whereas student A has Test 1-3

    Does this make sense?
    Basically, the table can expand both ways, there isn't a fixed amount of columns so I'm not sure how to record this or design it.

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the database I have a student table and a tests table. The tests table is linked with a many to one relationship. Each student can have many tests but each test can only have one student. I just don't know how to record this in the format of the table in the first post.

  3. #3
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,268
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    I notice also that each test seems to have multiple runs using different time values, so in addition to the students and tests tables, you'll probably also need a test_runs table.

    Code:
    students
    -----------------------------
    | id | name | whatever_else |
    -----------------------------
    
    tests
    --------------------------
    | id | student_id | name |
    --------------------------
    
    test_runs
    -------------------------------
    | id | test_id | time | value |
    -------------------------------

  4. #4
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    I notice also that each test seems to have multiple runs using different time values, so in addition to the students and tests tables, you'll probably also need a test_runs table.
    [/code]
    Thanks, actually a buddy helped me out and I think we have it.

    Code:
    Students
    ---------------------------------------------------
    | id | name |
    ---------------------------------------------------
    
    Tests
    ---------------------------------------------------
    | id | name | student_id | other test information |
    ---------------------------------------------------
    
    Tests_Taken
    ---------------------------------------------------
    | id | time |
    ---------------------------------------------------
    
    Tests_Taken_Lookup
    ---------------------------------------------------
    | tests_taken_id | tests_id | grade |
    ---------------------------------------------------
    I think this works, see any problems with it?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    I think this works, see any problems with it?
    too many ids are messing up your head
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    too many ids are messing up your head
    I don't follow, Rudy

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you have...

    Tests.id
    Tests_Taken.id
    Tests_Taken_Lookup.tests_taken_id
    Tests_Taken_Lookup.tests_id

    the last two, explains them please

    in particular, explain why they are different from each other
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you have...

    Tests.id
    Tests_Taken.id
    Tests_Taken_Lookup.tests_taken_id
    Tests_Taken_Lookup.tests_id

    the last two, explains them please

    in particular, explain why they are different from each other
    In short, the last two are foreign keys for relationships. I'm about to jump into a meeting for a couple hours so I'll try to answer this the best I can when I get out.

  9. #9
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    Thanks, actually a buddy helped me out and I think we have it.

    Code:
    Students
    ---------------------------------------------------
    | id | name |
    ---------------------------------------------------
    
    Tests
    ---------------------------------------------------
    | id | name | student_id | other test information |
    ---------------------------------------------------
    
    Tests_Taken
    ---------------------------------------------------
    | id | time |
    ---------------------------------------------------
    
    Tests_Taken_Lookup
    ---------------------------------------------------
    | tests_taken_id | tests_id | grade |
    ---------------------------------------------------
    I think this works, see any problems with it?
    What is the purpose to have Tests_Taken table? Why not store time in Tests_Taken_Lookup table?
    which should have student_id, tests_id, grade and test_time

  10. #10
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    The front end is going to look like this:

    Code:
    Testing Information For student A
    | Time |  Test 1  |  Test 2  |  Test 3 |
    | 8:00 |    3     |    5     |    2    |
    | 4:00 |    7     |    2     |    3    |
    | 2:00 |    4     |    5     |    1    |
    | 9:00 |    7     |    0     |    1    |
    The table doesn't make much sense to me. Test 1, Test 2 and Test 3 happen to have the same duration on so many rows?


    From what I understand you should have:

    Student | Test | Time | Grade

    which means
    - a table for Student: student_id, student_name,
    - a table for Test: test_id, test_name
    - a table for Test_Taken: student_id, test_id, test_time, test_grade

    Code:
    Testing Information For student A
    
    Test1
    | Time |  Grade |
    | 8:00 |    3   |
    | 4:00 |    7   |
    | 2:00 |    4   |
    | 9:00 |    7   |
    
    Test2
    | Time |  Grade |
    | 8:00 |    5   |
    | 4:00 |    2   |
    | 2:00 |    5   |
    | 9:00 |    0   |
    
    Test3
    | Time |  Grade |
    | 8:00 |    2   |
    | 4:00 |    3   |
    | 2:00 |    1   |
    | 9:00 |    1   |
    Obviously each test would have different durations, I just copy/paste it.

    The above shows you need a date and a time also, since duration of the test is not enough if you need further differentiation.


    EDIT
    It just dawned on me what you probably mean and want:

    - a table for Student: student_id, student_name,
    - a table for Test: test_id, test_name, test_time
    - a table for Test_Taken: student_id, test_id, test_grade (plus, of course, test_datetime, if needed)

  11. #11
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    Thanks, actually a buddy helped me out and I think we have it.

    Code:
    Students
    ---------------------------------------------------
    | id | name |
    ---------------------------------------------------
    
    Tests
    ---------------------------------------------------
    | id | name | student_id | other test information |
    ---------------------------------------------------
    
    Tests_Taken
    ---------------------------------------------------
    | id | time |
    ---------------------------------------------------
    
    Tests_Taken_Lookup
    ---------------------------------------------------
    | tests_taken_id | tests_id | grade |
    ---------------------------------------------------
    I think this works, see any problems with it?
    Yup. Rudy's pointed them out, but I think there's a conceptual problem here, and things are getting messed up.

    From what I can see, you only need three tables. Two which define your entities (tests and students) and one that essentially shows how they are related.

    So you need to determine
    1. What information is needed to define a student
    2. What information is needed to define a test
    3. What information is needed to show which student took what test when, and what were the results.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  12. #12
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all your responses, I've been busy today so I haven't been able to come back at this issue but I'm going to take another stab at it tonight with the suggestions posted and I'll update in the morning.

  13. #13
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Agreed with Dave. Only 3 tables need it

  14. #14
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by itmitică View Post
    The table doesn't make much sense to me. Test 1, Test 2 and Test 3 happen to have the same duration on so many rows?
    Oh, actually the time is the time the test was taken and not the test duration.

    Quote Originally Posted by DaveMaxwell View Post
    Yup. Rudy's pointed them out, but I think there's a conceptual problem here, and things are getting messed up.

    From what I can see, you only need three tables. Two which define your entities (tests and students) and one that essentially shows how they are related.

    So you need to determine
    1. What information is needed to define a student
    2. What information is needed to define a test
    3. What information is needed to show which student took what test when, and what were the results.
    Yes!

    I just had to look at it from a different perspective and this is exactly the way I concluded the design thanks to both Rudy and yourself. I did not in fact need four tables, just 3.

    My final design looks like this:

    Code:
    Students
    ----------------------------
    id | name
    ----------------------------
    
    Tests
    ----------------------------
    id | name | 
    ----------------------------
    
    Tests_Taken
    ----------------------------
    students_id | tests_id | time
    ----------------------------

  15. #15
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Much better, and makes more logical sense, doesn't it?
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  16. #16
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure does!

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ryan Mortier View Post
    My final design looks ...
    ... awesome

    you really took "too many ids are messing up your head" seriously, thanks

    now there are just enough ids

    like dj albert said, "make things as simple as possible, but no simpler, dawg"

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


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
  •