# Struggling with designing a part of my database. — retained record

> Retained reddit record from reddit:database.

Canonical: https://highsignal.app/data/records/ff279c033a80fe87

[← sources](https://highsignal.app/data)


source: [reddit](https://highsignal.app/data/reddit) (reddit:database)  published 2026-11-02 10:59 ingested 2026-11-03 02:41

[original source ↗](https://www.reddit.com/r/Database/comments/1xlu6tf/struggling_with_designing_a_part_of_my_database/)

## retained text



```
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?
```



document fetched 2026-11-03 00:39 · canonical https://reddit.com/r/Database/comments/1xlu6tf/struggling_with_designing_a_part_of_my_database
