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