25. March 2021
Today, I want to share some database design tips with a case study, that I learned from my mentor. Every week or alternate week He gives us a use case and tells us to design a database for it. Sometimes the Data is in the Tabular format, Sometimes it is a simple word document. Here I am sharing one of that use case
My mentor gave us a document that consists of A set of Survey Questions divided into Sections, and there are multiple options for those Questions. So They were all multiple-choice questions.
Sample Raw Data: Below is the sample data. So we need to design data to store these Questions and Their Answers and User Responses.
Database Assignment.docx (17.78 kb)
You get requirements from the client, and then you start to design a database for the clients. So according to those requirements, you categorized the data. So here in the case study lets assume some of the requirements
- Need to store every user response and user info
- Need to store questions and answers
- Every Question can repeat in multiple sections
Key Points to keep in mind Before Database Design:
- Performance of Database Queries
- Cost of maintaining it
Analysis of the sample data:
- There are multiple sections in it, meaning our Survey has been divided into Sections.
- Every Section has several Questions there. And every question has a set of answers.
- Some questions have answers as “yes”, “no”, “sometimes” and those Answers are repeated for the Many Questions.
- Some questions have a set of the answer which are not repeating for any other questions.
Possible Real-life Data:
- In our sample data, we only have multiple-choice questions. But in surveys, we can get multiple choice questions, single choice questions, and subjective questions/ user inputs
- User information
Here is my design for the given data.
Below are the tables, their columns, and their relationship.
PK- Primary Key
FK- Foreign Key
- UserInfo- to store User Info
- UserResponse- to store user responses
- QuestionInfo and AnswerInfo- For Question and answer information, Types are to differentiate the question and answer type.
- SectionQuestionRelation- To allow "many to many" relation between Section and Question[ To support one question in multiple sections]
- QuestionAnswerRelation - So multiple Answers can be related to multiple questions. And if any Question is subjective it will only present in the QuestionInfo table and not in the QuestionAnswerRelation table.
- OfferedAnswer- For User Input Answers
Note: Here I have shared my design. There can be multiple correct designs for the current set of data and requirements.
Thank You, Hope this is helpful.