CODEWITHSUNDEEP
sqlpostgresqldatabase-design
Vadim Sheremetov
Vadim Sheremetov

Reputation: 590

PostgreSQL different datatypes in one column

I have a database which contains 2 tables - tests and questions(for those tests). enter image description here

Questions table has a column called right_answer which might be an array of strings or a single string. enter image description here

So, I'm wondering what is the best approach to store data in this case? Should I have several questions tables for an each answer type or there's some other way?

Maybe I can store my right_answer using only one table somehow?

Upvotes: 0

Views: 394

Answers (1)

user330315
user330315

Reputation:

A properly normalized model should always be your first approach:

create table questions
(
  id       integer generated always as identity primary key,
  type     text not null,
  test_id  bigint references tests
);

create table answers
(
  id               integer generated always as identity primary key,
  question_id      integer not null references questions,
  answer           text not null,
  is_right_answer  boolean not null
);

Upvotes: 2

Related Questions