PHP/MySQL: Handling Questionnaire Input

I have a questionnaire for users to be matched by similar interests: 40 categories, each with 3 to 10 subcategories. Each of the subcategories has a 0 - 5 value related to how interested they are in that subcategory (0 being not even remotely interested, 5 being a die-hard fan). Let's take an example for a category, sports:

<input type="radio" name="int_sports_football" value="0">0</input>
<input type="radio" name="int_sports_football" value="1">1</input>
<input type="radio" name="int_sports_football" value="2">2</input>
<input type="radio" name="int_sports_football" value=开发者_StackOverflow中文版"3">3</input>
<input type="radio" name="int_sports_football" value="4">4</input>
<input type="radio" name="int_sports_football" value="5">5</input>

With so many of these, I have a table with the interest categories, but due to the size, have been using CSV format for the subcategory values (Bad practice for numerous reasons, I know).

Right now, I don't have the resources to create an entire database devoted to interests, and having 40 tables of data in the profiles database is messy. I've been pulling the CSV out (Which looks like 0,2,4,1,5,1), exploding them, and using the numbers as I desire, which seems really inefficient.

If it were simply yes/no I could see doing bit masking (which I do in another spot – maybe there's a way to make this work with 6-ary values? ). Is there another way to store this sort of categorized data efficiently?

You do not do this by adding an extra field per question to the user table, but rather you create a table of answers where each answer record stores a unique identifier for the user record. You can then query the two tables together using joins in order to isolate only those answers for a specific user. In addition, you want to create a questions table so you can link the answer to a specific question.

table 1) user: (uniqueID, identifying info)

table 2) answers: (uniqueID, userID, questionID, text) links to unique userID and unique questionID

table 3) question: (uniqueID, subcategoryID, text) links to uniqueID of a subcategory (e.g. football)

table 4) subcategories: (uniqueID, maincategoyID, text) links to uniqueID of a mainCategory (e.g sports)

table 5) maincategories: (uniqueID,text)

An individual user has one user record, but MANY answer records. As the user answers a question, a new record is created in the answers table, storing the uniqueID of the user, the uniqueID of the question, and the value of their answer.

An answer record is linked to a single user record (by referencing the user's uniqueID field) and a single question record (via uniqueID of question).

A question record is linked to a single subcategory record.

A subcategory record is linked to a single category record.

Note this scheme only handles two levels of categories: sports->football. If you have 3 levels, then add another level in the same manner. If your levels are arbitrary, there may be some other scheme more suited.

okay, so, given that you have 40 categories and let's assume 10 subcategories, that leaves us with 400 question-answer pairs per user.

now, in order to design the best intermediary data storage, I would suggest starting out with a few questions:

1) what type of analysis will I need
2) what resources do I have
3) is this one time solution or should it be reused in future

Well, if I were you, I would stick to very simple database structure e.g.:

question_id | user_id | answer

if I would foresee more this kind of polls going on with same questions and probably having same respondents, I would further extend the structure with "campaign_id". This would work as raw data storage which would allow quick and easy statistics of any kind.

now, you said database is no option. well, you can mimic this very same structure using arrays and create your own statistical interface that would work based on the array storage type, BUT, you would save their and your time if you could get sql. as others suggest, there is always sqlite (file based database engine), which, is easy to use and setup.

now, if all that does not make you happy, then there is another interesting approach. if data set is fixed, meaning, that there are pretty much no conditional questions, then, given that you could create question index, you could further create funny 400byte answer chunk, where each byte would represent answer in any of the given values. then what you do is you create your statistical methods that, based on the question id, can easily operate with $answer[$user][$nth] byte (or $answer[$nth][$user] -- again, based on the type of statistics you need)

this should help you get your mind set on the goal you want to achieve.

I know you said you don't have the resources to create a database, but I disagree. Using SQL seems like your best bet and PHP includes SQLite (http://us2.php.net/manual/en/book.sqlite.php) which means you wouldn't need to set up a MySQL database if that were a problem.

There are also tools for both MySQL and SQLite which would allow you to create tables and import your data from the CSV files without any effort.

maybe I am confused but it seems like you need a well designed relational database. for example:

tblCategories (pkCategoryID, fldCategoryName)

tblSubCategory (pkSubCategoryID, fkdSubCategoryName)

tblCategorySubCategory(fkCategoryID,fkSubCategoryID)

then use inner joins to populate the pages. hopefully this helps you :)

i consider NoSQL architecture as a solution to scaling MySQL field in agile solutions.

To get it done asap, I'd create a class for "interest" category that constructs sub-categories instance which extends from category parent class, carrying properties of answers, which would be stored as a JSON object in that field, example:

{
     "music": { // category
        "instruments": {    // sub category
            "guitar": 5,        //intrest answers
            "piano": 2,
            "violin": 0,
            "drums": 4
        },
        "fav artist":{
            "lady gaga": 1,
            "kate perry": 2,
            "Joe satriani": 5
        }
     }
     "sports": {
        "fav sport":{
            "soccer": 5,
            "hockey": 2,
        }
        "fav player":{
            "messi": 5,
            "Jordan": 5,
        }
     }
 }

NOTE that you need to use "abstraction" for the "category" class to keep the object architecture right