Most elegant solution for humungous problem

Question

I'm working on a site where a user could select certain dates that apply to them, e.g Date 1,Date 2, Date 3, etc.

Each date will have certain questions belonging to it, so if the customer checked off 'Date 1' to indicate that this date applies to him, he'll then see a bunch of textboxes asking him about Date 1 and how it applies to them. Same goes for all other dates.

Date 1 and 2 and several questions, but the remaining dates have just 1 question.

These dates and their answers will later be used to create personalized reminders for the customer and sent out to him.

I'd also like a design which makes it simple (or as simple as possible) to add additional dates and fields.

My question is, what's the best way to store all the dates and their answers related to the user in the database? I was thinking that in the user table, I have boolean columns from Date 1 - Last date (obviously they're not actually named date 1, date 2 etc). If the column for Date 1 is set to 0, it means the customer didn't check it off, and if its 1 then it means he did and he answered the questions for it.

Regarding the actual storage of the dates, I'm considering these two options:

1) 1 table for each date, with columns for every question asked for that date and a user_id column. So in Date_1 table I'll have columns like Q1_name, Q2_name and the answers of the questions that the user gave.

But I want something more elegant because 1), it will make it a pain to fetch all the user's answers when calculating what info. applies to them when sending the personalized emails. 2) Some dates have only 1 question, so it'll be ugly to create a full-blown table for them.

2) A user_answer table, with the columns:

user_id,date_name,question_name,answer_val

2 seems the most elegant so far, but there should be a better solution. Any thoughts?

Answer 1

Option 2 seems close to the good solution, but I'd replace date_name and question_name with date_id and question_id.
An optimal, and simplest solution that enables adding new questions and dates seems to be:
1. Dates table with the fields date_id, title, date.
2. Questions table with the fields question_id, date_id, title (and possibly type off the answer and description).
3. Answers table with question_id and answer.

You'll also need to decide whether or not you have questions that are common to several dates, and what to do in that case. (you may want two different answers, or a common answer). I'd recommend against the first solution, it'd make the question not dynamic - you'd have to change the sql and table structure for each change in the questions or dates.

Answer 2

I think you've pretty much got it, the only thing is you could possibly separate the question and date names into a separate table, giving you the following schema:

User: id, ... (name, date of birth, favourite food, etc)
DateName: id, name (called it DateName so no conflicts with the word Date, which could mean something else in SQL)
Question: id, date_id, text
UserAnswer: user_id, question_id, answer_val

It's not necessary to split them up, but there are three reasons to do it: 1. Lookups on integers (like question_id) as opposed to text (like question_name) is a lot faster because integers are smaller and fixed in size. 2. If you change the text to a question (like to correct a spelling mistake), you only need to change a single entry in Question, instead of on every single row. 3. Because you're storing each bit of text only once, you save a lot of space. Storing 1000 ints are smaller than storing 1000 strings. Well as long as the strings are longer than a few character that is.

Besides that I think that could work quite well.

Answer 3

It sounds like you want something like the following (basic) table structure:

• User - userId, userInfo
• Date - dateId, dateInfo
• Question - questionId, questionInfo
• UserDate - userId, dateId - this stores all dates that apply for a given user and represents the many to many relationship between Users and Dates - a user can have many dates and a date can have many users
• DateQuestion - dateId, questionId - this stores all questions that apply for a given date and represents the many to many relationship between Dates and Questions - a date can have many questions and, I assume, a question can be used against more than one date
• UserResponse - userId, questionId, questionResponse - this stores all user responses to any questions asked. If you need to know which date the question applies to, assuming they can answer the same question more than once for multiple dates, add a dateId column.