So i have to do a project for a university course.
This project is about the creation of multiple choices (MC).
So one user can submit a number of MCs (packed in a quiz), and then another user can take the said quiz.
Each question and each answer can either be in the form of text or they can be in the form of an image or even a video.
So how should i design my SQL schema?
I have the tables:
Question(questionID,..)
Answer(answerID,...)
And i don't know how to associate them with the Media Table that has the plaint text or the link if it's an image or video.
Should i just have one table like:
Media(mediaID,type,content), where type has the value 0 for text,1 for image, 2 for video
and then
hasMedia(mediaID,correspondingID) where it would either be the id of the question or the answer.
or make the table hasMedia like this:
hasMedia(mediaID,questionID,answerID) and put a 0 in the questionID or the answerID, if it is not referred to them?
OR make different tables that hold the Media for the Questions and different Media tables for the Answers like:
MediaQuestion(mediaID,type,content)
hasMediaQuestion(mediaID,questionID)
and
MediaAnswer(mediaID,type,content)
hasMediaAnswer(mediaID,questionID)
Or something different entirely??
This is the schema for the last scenario
Thanks for your time!