Questions on how I should handle questions and answers in a postgres database

Viewed 10

I'm building an app with Postgres where the client can create questions for their applicants to answer. However, I want to make sure I'm designing right before I get into too much technical debt. Mainly, I have two questions:

  1. How should I represent answers that are either a string or an array of strings? I was previously thinking about just having the schema be an array, and then text questions would just reference the 0 index. But that seems to be not kosher. Below I broke it up into different tables.
  2. How could I represent schemas that the answers must adhere too? Examples include fitting some enum, being a date, or being so many characters. I figure the easiest way to do this would to just store a regex string. Any thoughts?
  3. How can I represent control flow for these questions? Like if the user answers that they're over 30, show this question, otherwise show another question. I don't really know how to go about that.

Here's a picture of my diagram enter image description here

Table Questions {
  id int [pk]
  title varchar
  subText varchar
  type Enum
}

Table Question_Choices {
  id int [pk]
  question_id int [ref: > Questions.id]
  value varchar
}

Table Answers {
  id int [pk]
  question_id int [ref: > Questions.id]
  value varchar
  timeSpent int
}

Table Answer_Log {
  id int [pk]
  answer_id int [ref: > Answers.id]
  timestamp timeStamp
}

Table AnswerString {
  id int [pk]
  answerId int [ref: - Answers.id]
  value varchar 
}

Table AnswerArr {
  id int [pk]
  answerId int [ref: - Answers.id]
  values varchar[]
}
1 Answers

How should I represent answers that are either a string or an array of strings? I was previously thinking about just having the schema be an array, and then text questions would just reference the 0 index. But that seems to be not kosher. Below I broke it up into different tables.

--> Assuming the answer by the client will be of simple text then string is fine.

How could I represent schemas that the answers must adhere too? Examples include fitting some enum, being a date, or being so many characters. I figure the easiest way to do this would to just store a regex string. Any thoughts?

--> answer may not have any character limit, and you can reduce no.of tables for instace (answer_log, answer_schema, asnwer arr, answer string), you can combine all these table and put those fields in answers. (db calls will add latency)

How can I represent control flow for these questions? Like if the user answers that they're over 30, show this question, otherwise show another question. I don't really know how to go about that.

--> A question can have multiple answers (by same or distinct user) its like one to many, Like if the user answers that they're over 30 ( you can get this from answers table by an agrregation query)

Related