Good Afternoon,
I'm working on the table layout for a SQL database that will store individual and test information. This includes individual identifying information like Name, medical id, date of birth, etc, and test specific information - specifically raw and standard scores from a variety of psychometric tools. For the purposes of this question, I'll be providing the following sample tables:
Indiv_Information; Test_Log; Test_1; Test_2; Test_3
I have some questions about the best way to link individual information to these test scores. Option 1 looks like this:
In this example each test logged is linked to the medical id of the individual who took the test. My only concern with this layout is that if I want to create a display a list of all tests a user has taken I have to scan through all of these tables, and currently there are 20+
Option 2: Create a test log table and link that to multiple test tables that contain the scores for each test. Looks like this:
It's much easier to generate a list of all tests each individual has taken with this setup, but I'm not sure this is a good setup for reporting. The main problem here is that the field "assessment_id" is linked to multiple tables, and the only way to specify which score data to join to the test log table is to use "test_name_id" direct traffic. I don't even know if this is possible.
I'm looking for opinions on which of these two layouts to choose. Am I close to something optimal, or completely off base. I'm not too experienced at database design yet.
Cheers, and have a great weekend!
