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:
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.
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.
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.
students
-----------------------------
| id | name | whatever_else |
-----------------------------
tests
--------------------------
| id | student_id | name |
--------------------------
test_runs
-------------------------------
| id | test_id | time | value |
-------------------------------
Thanks, actually a buddy helped me out and I think we have it.
Students
---------------------------------------------------
| id | name |
---------------------------------------------------
Tests
---------------------------------------------------
| id | name | student_id | other test information |
---------------------------------------------------
Tests_Taken
---------------------------------------------------
| id | time |
---------------------------------------------------
Tests_Taken_Lookup
---------------------------------------------------
| tests_taken_id | tests_id | grade |
---------------------------------------------------
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.
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
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
What information is needed to define a student
What information is needed to define a test
What information is needed to show which student took what test when, and what were the results.
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.
[QUOTE=itmitică;5137965]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?
[/QUOTE]
Oh, actually the time is the time the test was taken and not the test duration.
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:
Students
----------------------------
id | name
----------------------------
Tests
----------------------------
id | name |
----------------------------
Tests_Taken
----------------------------
students_id | tests_id | time
----------------------------