How can I design a database schema for polls in a chat app?

Question

I am creating a chat bot for a chat application, people can run a command and specify a question and possible answers for a poll, after that emoji reactions are added to the author's message, my bot removes the all reactions that get added and stores the vote in the database, making the poll anonymous, my question is how can I design my schema? I am using a Postgres database, my idea was something like:

// Creating a poll from those values
const question = "what is the best color?"
const answerCache = ["red", "green", "blue"]

// Helpers for mapping emoji reactions to a index
const emojis = ["emojiForFirstChoice", "emojiForSecond", "emoijForThird"]

const getIndexFromReaction = r => emojis.findIndex(e => r === e)

// Example of reaction
const authorId = "someId"
const reaction = "emojiForFirstChoice"
const index = getIndexFromReaction(reaction)

@Entity()
export class Question extends BaseEntity {
  @PrimaryColumn()
  messageId!: string;

  @Column()
  authorId!: string;

  @Column()
  question!: string;

  @Column("string", { array: true })
  answerCache!: string[];

  @OneToMany(
    () => Answer,
    answer => answer.question
  )
  answers: Answer[];
}

Then I would create a seperate schema

@Entity()
export class Answer extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  authorId: string;

  @Column()
  index: number;

  @ManyToOne(
    () => Question,
    question => question.answers,
    {
      onDelete: "CASCADE",
    }
  )
  question: Question;
}

This way, whenever somebody adds a reaction, I store them in the Answer table, when they change their answer, I update the index of the column, when the poll ends, I collect all indexes, since the answers are stored in an array I can count every answer using the index, some possible issues I saw / questions I have:

• Is it save to store the original answers (answerCache) like this? Does Postgres guarantee the right order?
• Should I make this fully relational instead? If so, what would be the best way to go about doing that?
• Can I combine all of this into a single Poll entity but still have the same behaviour?

Answer 1

The way you're storing it is just fine.

Because the possible answers (answerCache) are indeed an array, you could keep them like that. Creating a table just for them would not bring any real advantage here, unless you're looking to do some analysis like "what are the most common answers".

In terms of keeping the order, I couldn't find anything that said it doesn't, but I would advise you test it out first. Another alternative would be to save the answerCache as a string, and when you need to use the array convert it back using JSON.parse

You could combine all of this into a single table, but you would probably need to save json or jsonb columns to keep all the information about who answered.