There are 4 online programs(A, B, C, D) for users to attend.
Users are only allowed to attend one program at the same time.
They can attend other programs given that they have no ongoing program.
In the program, there are around 50 pages in each program and some pages require user to answer the question. For example, page 3 may have 3 multiple-choice questions, page 11 have 1 fill-in-the blank question
users need to answer some questions which may be
multiple choice or
fill-in the blank questions, which are opinion-based(no correct answer).
They can navigate to previous/next page for answering questions.
Also, I need to record the last page users visited so that they can come back anytime.
Database Schema Design
I've made a initial design for all tables I need.
User -userId(primary key) -username -password ...
Program -programId(primary key) -type (A/B/C/D) -currentPage -startDate -endDate
Question -questionId(primary key) -type(multipleChoice/fillInTheBlank) -questionText
Question-Answer -Id(primary key) -questionId(foreign key of Question table) -answerText
Above Schema is all I can think of, I have no idea how to manage the relationship between above table.
Would Anyone provide some ideas to me?