Posts: 2
Registered: ‎07-29-2020

Need advice on database layout!!

I might have made a mess with my current db layout. Currently i have four (4) tables subjectslevelsquestions and choices. Subject are example science, cars, sports and so on. Levels have a relation to subjects with a column named subject_id, and are "sub-subjects" of subjects, for example subject science can have "chemistry", "physics" and "astronomy" as levels. And each levels ofc has questions. So table questions have a relation to both subjects and levels with columns subject_id and level_id. And table choices has a relation to questions with column question_id. This setup works fine, but i have notices that it is terrible if i want to have the same question in another "level". I have to make a duplicate question, instead of having some sort of lookup table. And the same goes for images. Each table (!choice) has column image were i store the image filename. Lets say i want to change an image, i need to change filename in every image column that has that filename, instead of changing filename in a lookup table. Im no SQL nor database guy and having a hard time grasping how i should design the db. What i want is a flexible and scaleable db and it doesent feel like my current design is either. So how would you guys design your db if you was making a quiz application, with lots of different subjects, sub-subjects. And the ability to reuse questions etc? (And how about the answers?