# Advice on database structure design (survey tracking) **URL:** \ **Category:** Factual Questions\ **Created:** [October 13, 2006, 2:27pm UTC](https://boards.straightdope.com/t/advice-on-database-structure-design-survey-tracking/376306 "2006-10-13T14:27:35Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Rhythmdvl](https://avatars.discourse-cdn.com/v4/letter/r/85f322/32.png) [@Rhythmdvl](https://boards.straightdope.com/u/Rhythmdvl)\ **Post date:** [October 13, 2006, 2:27pm UTC](https://boards.straightdope.com/t/advice-on-database-structure-design-survey-tracking/376306/1 "2006-10-13T14:27:35Z")
Hello, I hope I can get a bit of advice regarding database design. I’m a complete amateur at this, but being the only one with _some_ skills, it’s fallen to me to do the implementation. Hopefully I can explain my question in an organized fashion: Information to track There are five (though there will soon be more) surveys. Each survey has some slight variation. In short, the survey questions can be divided into three categories: [ul] [li] Biographical information (name, address, etc.)[/li][li]Survey questions (e.g. “do you like filling out surveys?”, and “do you own any pets?”)[/li][li]Item checkboxes (e.g. "Which of the following pets do you own Cat\_\_ Dog\_\_ Llama \_\_ Platypus \_\_)[/li][/ul] The biographical information fields will never change and are identical across surveys. The survey questions _may_ change in the future, but right now half are all identical across all surveys and half vary between surveys, though there is some overlap. The item checkboxes will _definitely_ change over time. There is some overlap between surveys (i.e. “cat” appears on all surveys at the moment, but some have “cat” and “dog” while others have “cat” and “llama”). How it’s set up now There is a main table to collect the common biographical information, and a sub-table for _each_ individual survey. [ul] [li]tbl\_main has all biographical fields and all questions that are identical on all surveys[/li][li]tbl\_survey **1** … **2** … **3** … **N** have fields for those questions that vary and for the checkboxes of the respective survey. There is also a foreign key that relates the record to tbl\_main. [/li][/ul] This seems somehow inelegant, but I’m not quite proficient enough to say why. It feels wrong to have several fields repeating across sub-tables. What if I want a query that, for example, pulls all people in California who don’t like surveys and have cats or llamas? I think the problems with that are somewhat self evident: I’d need to know/remember the fields of _all_ tables in order to know which to include in the query. But I’m not sure what the best way to redesign the structure would be (hence, why I’m posting here). I’m leaning towards just two tables: One table with the unchanging, permanent biographical information in it. Then a second table with a field for each question, regardless of whether the question repeats across surveys. However… wouldn’t doing it this way mean that over time, the second table will grow and grow and grow in the number of fields? Or should each question get its own table? That is, a tbl\_cats with the only field being the primary key of tbl\_main to relate it to a responder. Each one-field table would get populated by a responder-ID. However… wouldn’t that mean I’ll eventually end up with hundreds of tables? Some complicated queries might be a bit, well, extra complicated, no? So… are any of the above methods the “proper” way to design this structure? Is there another, better way I should go about this? Oh, if it makes a difference, this database is built with PHP and MYSQL, interacting through the web. Thanks for any help you may have—even if it’s just an encouraging word! Rhythm ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [October 13, 2006, 2:39pm UTC](https://boards.straightdope.com/t/advice-on-database-structure-design-survey-tracking/376306/2 "2006-10-13T14:39:51Z")
I would suggest: Tbl\_Main (biographical stuff) Tbl\_Questions (one record per question) Tbl\_Survey - with QuestionID, PersonID as foreign keys, and a field for the answer If your questions are multi-choice and not the same range of choices per question, then I think you need Tbl\_Responses, with QuestionID as a foreign key, each row describing one possible response to the question. (in this case, the answer field in Tbl\_Survey also becomes a foreign key to ResponseID. That way, when you add a new question, you only need create one new record in Tbl\_Questions and a set of records in Tbl\_Responses defining each possible answer - you don’t have to create any new tables/structures, nor do you have to modify your application to deal with newly-added tables/structures. ---
**Author:** ![ZipperJJ](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zipperjj/32/211_2.png) [@ZipperJJ](https://boards.straightdope.com/u/ZipperJJ)\ **Post date:** [October 13, 2006, 2:50pm UTC](https://boards.straightdope.com/t/advice-on-database-structure-design-survey-tracking/376306/3 "2006-10-13T14:50:18Z")
I am not sure I follow exactly but this is what I would suggest. 1. Users table to track respondants. ID, Address, other “biographical information.” Also date and perhaps IP data. 2. Survey table. ID, Survery name only. Relate it to Users table with an ID (user fills out survey 1, row is added to Users table with SurveyID = 1) 3. Questions table. One row per question. Give it an Answers column and put multiple-choice answers in the field if available with a separator you can use later, in your form. (Cat,Llama,Dog - use as an array in PHP later). Also give it a SurveyID and perhaps a SortOrder column. So you can dynamically make your forms. 4. UsersQuestions table, to hold responses. SurveyID, UserID, QuestionID, Answer. When you tabulate your data for reporting, use joins to get everything to display nice for you. The nice thing about this structure: very dynamic and very clean (using FK’s). Easy as heck to add on to without disrupting data and lets you re-use questions. The bad thing about this structure: Multiple choice answers will show up like (1,2,3,‘cat,dog’) and you will need to do some string-based querying to extract how many people answered that they have a cat, even if some say “cat,llama” and some say “cat,dog” But…it’s one idea. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [October 13, 2006, 2:54pm UTC](https://boards.straightdope.com/t/advice-on-database-structure-design-survey-tracking/376306/4 "2006-10-13T14:54:18Z")
[QUOTE=ZipperJJ] 3. Questions table. One row per question. Give it an Answers column and put multiple-choice answers in the field if available with a separator you can use later, in your form. (Cat,Llama,Dog - use as an array in PHP later). [/QUOTE] I think it’s a really bad idea to store multiple pieces of data in a single field. Maybe it is common practice with PHP and makes it easy to construct an array, but I’m pretty sure it’s a big no-no as far as formal standards of database normalization are concerned. ---
**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\ **Post date:** [October 13, 2006, 3:00pm UTC](https://boards.straightdope.com/t/advice-on-database-structure-design-survey-tracking/376306/5 "2006-10-13T15:00:14Z")
You don’t even _necessarily_ need Tbl\_Survey. You can dedicate a field in Tbl\_Questions, and one in Tbl\_Responses, to a concatenation of the unique identifier in Tbl\_Main + TimeStamp (Date & Time Taken). You haven’t described the process by which the possibly-different valuelist set of checkbox responses, and the half-different set of questions, are determined; I’m assuming via a script? Anyway, just assign as a temporary variable the TimeStamp string and then auto-enter that in conjunction with the Tbl\_Main keyfield and that would suffice to _define_ the “survey” without really needing a table for it. (It’s not like it’s a Physics Exam where you assign grades or otherwise attach a set of variables to the survey _as_ survey; it really only consists of an association between the other tables) ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [October 13, 2006, 3:21pm UTC](https://boards.straightdope.com/t/advice-on-database-structure-design-survey-tracking/376306/6 "2006-10-13T15:21:12Z")
To clarify what I said above, I’d do it like [this](http://www.gurman.co.uk/stuff/db.png) ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [October 13, 2006, 4:09pm UTC](https://boards.straightdope.com/t/advice-on-database-structure-design-survey-tracking/376306/7 "2006-10-13T16:09:18Z")
> [@ZipperJJ](#): > > The bad thing about this structure: Multiple choice answers will show up like (1,2,3,‘cat,dog’) and you will need to do some string-based querying to extract how many people answered that they have a cat, even if some say “cat,llama” and some say “cat,dog” If you do it that way, it’s not even in 1NF, and that throws off a lot of optimization/error-proofing techniques. I can’t recommend that. My solution is very similiar to **Mangetout** ’s structure, but there are a couple differences: USERS( pk\_USER\_ID, BIOGRAPHICAL\_FIELD\_1, …, BIOGRAPHICAL\_FIELD\_N ); SURVEYS( pk\_SURVEY\_ID, TITLE, etc. ) SURVEY\_USER\_COMBINATIONS( pk\_SURVEY\_USER\_COMBO\_ID, fk\_SURVEY\_ID, fk\_USER\_ID, TIMESTAMP ); MULTIPLE\_CHOICE\_ANSWERS( pk\_ANSWER\_ID, fk\_SURVEY\_ID, QUESTION\_ID, ANSWER\_VALUE ); SURVEY\_MULTIPLE\_CHOICE\_ANSWERS( pk\_RECORD\_ID, fk\_SURVEY\_USER\_COMBO\_ID, fk\_ANSWER\_ID ); SURVEY\_CHECKBOX\_ANSWERS( pk\_RECORD\_ID, fk\_SURVEY\_USER\_COMBO\_ID, BITMASK ); I hope the relationships are clear from the foreign keys. The basic idea is that you have one table to identify surveys and one to identify users. You also have a table to identify which users have taken which surveys, which is where most of your data will be joined to. Each user’s answers to each survey are split among two tables because the datatypes for multiple choice and checkbox questions are different, so you’ll have to do some coding to get data in and out of the table. I’ve used a bitmask so that you only have one field per answer, which means you can use a union query to get raw data.