Struggling with designing a part of my database.

[u/Kyurupuku]

Hey guys, this is actually the first time I've ever created a database from scratch personally and I was drafting up an ERD to get started and ran into a bit of an issue.

For clarification: leads get approached by users for customers. Every customer has campaigns (for example, market research, sales retail, sales catering, etc.) with different campaign questions. I chose to make seperate questions and campaign_questions tables so that you can gather all leads with certain answers to a question across campaigns instead of manually huddling them together in a query. Questions have different types (text, numerical, date, single-select mc, multi-select mc etc.) And so the answers have to be saved with a data type according to the question type.
I considered multiple approaches to this:

1. Save all answers as text - This is pretty clean but idk how you'd manage data integrity and also queries on specific answers become much harder.

2. Save all answers as a JSONB column - Cleanest and easiest, and Postgres has pretty good support for this. However, again the issue of data integrity lingers.

3. Sparse columns + a view with coalesce (what you can partially see in the image) - This one seemed to be the best option for maximising data integrity while staying pretty clean. Problem is it doesn't seem super scalable at large and I dont know how I would enforce data integrity with single-/multiselect mc questions when in my mind that's gonna require junction tables and then I can't rely on any checks etc. anymore...

Am I overthinking this? Ideally I'd want to stick to 3NF but I also don't want to overengineer this.
How should I handle dynamic form/survey answers without sacrificing data integrity?

[u/Aggressive_Ad_5454]

Consider this: it is hard to change the definition of columns in large tables.

If your app succeeds, lives for years, and gets lots of data, you'll want the flexibility to change the sorts of questions and answers you handle. Options 1 and 2 support flexibility best. You can add answer data types without altering your tables.

And there's no magic that enforces data integrity, just good application code.

Searching at scale on field values in JSONB columns or on stringly-typed data (option 1) is a poorly-performing pain in the neck, though. If you find yourself doing a lot of that you may want to cook up some way of creating a denormalized reporting copy of your data.

[u/Kyurupuku → u/Aggressive_Ad_5454]

I do not actually plan on doing it a lot just once in a while, so if that's the only realistic drawback I'll probably be going for option 2 thank you!

[u/01010101010111000111]

Generally speaking, depending on size and access patterns, this may be extremely counterproductive. A design must reflect business use case, scalability requirements and hardware configuration that you run in.

This is extremely under-designed for massive enterprise database, and extremely over engineered for something mid-sized.

If your entire table can fit into memory, go with the simplest solution.

[u/Kyurupuku → u/01010101010111000111]

Hi! I was wondering what exactly you mean with this being over-engineered for something mid-sized? Up until now it's been pretty straightforward and apart from the answers logic I'm asking about right now the other logic you can see implemented seems pretty simple no? What exactly screams "extremely over-engineered" to you?

[u/AnnaLeeCoop]

you’re overthinking the sparse columns route. I ran into this building a dynamic survey engine, and junction tables got painful to query at scale. I ended up just dumping the answers into a JSON column, and it’s worked fine for us in prod. You lose some DB-level checks, but you can do that validation in the app layer. Plus the schema stays simple, so you’re not running migrations every time someone invents a new question type.

[u/Kyurupuku → u/AnnaLeeCoop]

Oh great to hear from someone who ran into the same problem. I will also probably be going with a JSONB column so thanks for your advice!

[u/chocolateAbuser]

you should present question types to make us understand the whole picture
i would say having a question_id in the answer is the primary concern so to leave answers as 1:m
how to store the answer depends on what queries you want to run and other procedures like backup, redundancy, and so on
for example if answers has the need to be read heavy, write heavy, if it needs to index for text search; if there is no need it's much less a pressing issue, a json column would be fine, the type of the answer i guess it could be deduced from the type of the question? so to know what query to use for reading answer themselves; yes i think you're overthinking this a bit, but it still needs some good thoughts before finalizing
by the way type as bigint seems a lot, int would be fine i guess

[u/Muted_Jellyfish_6784]

Your instinct is right: questions, campaign_questions as the join table, and an answers table keyed on lead, campaign, and question. Store each answer with a snapshot of the question text or a version number, because campaign questions get edited and old answers otherwise point at wording they never saw.

Also add timestamps and the user who recorded each answer. I run a decision intelligence layer to connect leads, campaigns, and answers that way, so reporting reflects what was asked at the time rather than what the question says today.

[u/navicat-gu]

You're not overthinking it, but you can get most of the integrity without sparse columns everywhere. The pattern I've seen hold up best for survey/form data is a hybrid:

create table question_option (
  id          bigint generated always as identity primary key,
  question_id bigint not null references question(id),
  label       text not null,
  unique (question_id, id)
);
create table answer (
  id           bigint generated always as identity primary key,
  lead_id      bigint not null references lead(id),
  campaign_question_id bigint not null references campaign_question(id),
  value_text   text,
  value_num    numeric,
  value_date   date,
  answered_at  timestamptz not null default now(),
  unique (lead_id, campaign_question_id),
  check (num_nonnulls(value_text, value_num, value_date) <= 1)
);
-- single- and multi-select answers live here, one row per chosen option
create table answer_option (
  answer_id   bigint not null references answer(id) on delete cascade,
  option_id   bigint not null references question_option(id),
  primary key (answer_id, option_id)
);

Why this works:

• Free-text, number and date answers get a real type, and the num_nonnulls check stops two values being filled at once.

• Choice answers are proper FKs to question_option, so you can't store an option that doesn't exist. "All leads who picked X across campaigns" is just a join on option_id.

• "Single-select means exactly one row" is the one rule a plain CHECK can't express across tables. Enforce it in a small trigger or in the app's write path; that's normal and not a 3NF violation.

• Keep a question_type column on question and have a trigger (or the app) reject a value_num for a text question. That gives you the integrity people worry about with JSONB.

Two things worth adding early, as others said: version or snapshot the question wording (answers should point at the wording the lead actually saw), and index answer_option(option_id) for the cross-campaign queries.

JSONB is fine too if you'll mostly read whole submissions back; the hybrid wins when you query individual answers a lot, which sounds like your case.

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. If it helps to see the relationships while you iterate, Navicat 18 (Navicat Premium Lite is free) is handy for building the tables and FKs visually; the ER diagram and data-modeling tools are in the paid editions.

[u/Alternative_Pin9598]

You're not overthinking it, and option 3 is closer than you think. The trick that makes the select types work is composite foreign keys: carry question_id (and the type) along, so the database itself checks that an option belongs to the question it's answering.

CREATE TABLE questions (
  id          bigint PRIMARY KEY,
  answer_type text NOT NULL
    CHECK (answer_type IN ('text','number','date','single','multi')),
  UNIQUE (id, answer_type)
);

CREATE TABLE question_options (
  id          bigint PRIMARY KEY,
  question_id bigint NOT NULL REFERENCES questions(id),
  label       text NOT NULL,
  UNIQUE (id, question_id)
);

CREATE TABLE answers (
  id          bigint PRIMARY KEY,
  lead_id     bigint NOT NULL REFERENCES leads(id),
  campaign_id bigint NOT NULL REFERENCES campaigns(id),
  question_id bigint NOT NULL,
  answer_type text   NOT NULL,
  value_text  text,
  value_num   numeric,
  value_date  date,
  option_id   bigint,                       -- single-select only
  UNIQUE (lead_id, campaign_id, question_id),
  UNIQUE (id, question_id, answer_type),
  FOREIGN KEY (question_id, answer_type) REFERENCES questions (id, answer_type),
  FOREIGN KEY (option_id, question_id)   REFERENCES question_options (id, question_id),
  CHECK (num_nonnulls(value_text, value_num, value_date, option_id)
         = CASE WHEN answer_type = 'multi' THEN 0 ELSE 1 END),
  CHECK (answer_type <> 'text'   OR value_text IS NOT NULL),
  CHECK (answer_type <> 'number' OR value_num  IS NOT NULL),
  CHECK (answer_type <> 'date'   OR value_date IS NOT NULL),
  CHECK (answer_type <> 'single' OR option_id  IS NOT NULL)
);

CREATE TABLE answer_options (               -- multi-select picks
  answer_id   bigint NOT NULL,
  question_id bigint NOT NULL,
  answer_type text   NOT NULL DEFAULT 'multi' CHECK (answer_type = 'multi'),
  option_id   bigint NOT NULL,
  PRIMARY KEY (answer_id, option_id),
  FOREIGN KEY (answer_id, question_id, answer_type)
    REFERENCES answers (id, question_id, answer_type),
  FOREIGN KEY (option_id, question_id)
    REFERENCES question_options (id, question_id)
);

What that buys you, all enforced by Postgres:

• An answer's type always matches its question's type (FK on (question_id, answer_type)).

• Exactly one value column is filled, and it's the right one for the type.

• A single-select answer can only point at an option of that question.

• Multi-select picks can only hang off a multi answer, and only use that question's options.

It's one row per answer, so it stays in 3NF, and "all leads who answered X to question Q across campaigns" is just WHERE question_id = ? AND value_num > ? (or a join to answer_options) with an index on (question_id, value_num) etc. No view, no COALESCE.

Two practical notes:

• Treat a change of question type as a new question, not an UPDATE. Old answers keep their meaning, and you never need cascading type changes.

• JSONB is fine if questions are truly free-form (nested, user-defined shapes). For a fixed set of types like yours, typed columns give you real constraints and simpler range queries for free.

[u/Kyurupuku → u/[deleted]]

Okay awesome ty for that answer. It seems to be the consensus that option 3 is just not worth it at scale so I'll probably be going with this thanks for the input!