0

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:

example table structure 1

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:

example table structure 2

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!

0

Based on your information, let have a draft ERD like below:

TEST 
+ ID (PK)
+ NAME (UK)
+ ...

TEST_QUESTION
+ TEST_ID (PK)
+ QUESTION_ORDER (PK)
+ QUESTION_CONTENT
+ ...

INDIVIDUAL
+ ID (PK)
+ MEDICAL_ID
+ ...

TEST_LOG
+ ID (PK)
+ INDIVIDUAL_ID (FK to INDIVIDUAL)
+ TEST_ID (FK to TEST)
+ ...

If we're talking about to optimize the reporting, we should denormalize the database structure, that means to accept the data redundancy. In that case, we can merge TEST and TEST_QUESTION into one table.

Hope this help for an idea.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.