-1

Background

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?

New contributor
CCCC is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • Users are only allowed to attend one program at the same time. Cannot be provided by FK, trigger or client logic needed. – Akina Jun 8 at 7:19
  • You may want to look at how LMSes such as Moodle handle this. While that system is hardly a model of efficiency or clarity, it will answer a number of questions related to course management, quiz delivery, attempt tracking (retries), and the like. – matigo Jun 8 at 12:09

Your Answer

CCCC is a new contributor. Be nice, and check out our Code of Conduct.

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

Browse other questions tagged or ask your own question.