How to organize the correct database structure?

Question

MySQL

Hello.
More than once there was a situation when it was necessary to link tables in which the key in the next table is the key of the previous one + a unique column, for example, the task is as follows:
A database is required to store tests (users add as many tests as they like, add answer options themselves)
In the "Tests" database "there are Tables, in bold - the key of the table:
Users ( user_id , mail, etc.)
User_tests ( user_id , test_id , test_name, test_description, etc.)
User_test_questions ( user_id , test_id , question_id , question_self, etc.)
Or, for example, according to the same database scheme for storing training results (users create a training
program , say 3 days of training are added to it, exercises are added for each of them)
table with multiple foreign keys.
Is this the right approach, or maybe it's worth reconsidering something, can you recommend some literature on the database?
What is the best way to store answer options - in separate rows in the database or directly in the table with the question in a separate field, and then parse the output?
Interested in storing tests, where a disparate structure, i.e. one question can be checkboxes, the other radio buttons, the third - the user himself enters the answer. What is the best way to store it? Thank you.

Answer 1

Well, for "Tests_users" the "id_user" field is a foreign key to the user. It's okay here. And for "Questions_tests_users" storing "user_id" is meaningless, because. there is a "test_id" and you can always join.
About "one question can be checkboxes, the other radio buttons". You can enter a parameter inside the question table, which is responsible for the type of answer options - RADIO|CHECKBOX|INPUT. And depending on this type on the UI, draw the necessary fields for the options (if any).
Answer options are best stored in a separate table, destructured data is much easier to use. Just set the foreign key to "question_id"

Answer 2

See normal forms and coding style guide

Answer 3

Can you recommend some literature on the database?

Kroenke, David M., Auer, David J. Database Processing.
Connolly, Thomas M., Begg, Carolyn E. Database Systems: A Practical Approach to Design, Implementation, and Management.