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

[Ryan Mortier]

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.

[Ryan Mortier]

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.

[Jeff Mott]

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 |
-------------------------------

[Ryan Mortier]

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.

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?

[r937]

I think this works, see any problems with it?

too many ids are messing up your head

[Ryan Mortier]

too many ids are messing up your head

I don't follow, Rudy

[r937]

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

[Ryan Mortier]

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.

[gk53]

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 |
---------------------------------------------------

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

[itmitică]

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    |

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

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)

[DaveMaxwell]

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 |
---------------------------------------------------

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.

[Ryan Mortier]

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.

[gk53]

Agreed with Dave. Only 3 tables need it

[Ryan Mortier]

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.

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:

Students
----------------------------
id | name
----------------------------

Tests
----------------------------
id | name | 
----------------------------

Tests_Taken
----------------------------
students_id | tests_id | time
----------------------------

[DaveMaxwell]

Much better, and makes more logical sense, doesn't it?

[Ryan Mortier]

Sure does!

[r937]

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"