CODEWITHSUNDEEP
phpmysqlserialization
Michael
Michael

Reputation: 12816

To serialize array or not to serialize array: how to store a survey

I am building a survey-building system and am unsure how best to store the data. The two choices I can see I have are:

  1. To use a serialized array, or
  2. To store each element as a separate row in the relevant table

For example, by using the first I would store something like the following as a serialized array:

Array
(
    [name] => Survey
    [sections] => Array
        (
            [0] => Array
                (
                    [name] => Introduction
                    [pages] => Array
                        (
                            [0] => Array
                                (
                                    [text] => Please answer the following questions.
                                    [questions] => Array
                                        (
                                            [0] => Array
                                                (
                                                    [text] => Are you male or female?
                                                    [answers] => Array
                                                        (
                                                            [0] => Male
                                                            [1] => Female
                                                        )

                                                )

                                        )

                                )

                        )

                )

        )

)

The benefits I see to this are:

  1. It is centralized, and

  2. It is easy to edit within the script using a simple query like:

    UPDATE surveys SET data = '$serialized_array'

The main drawback I see to this is:

  1. It is difficult to edit outside the script (like when using Navicat, for example)

I also hear it's bad practice to store data as a serialized array. Although I guess this depends entirely on the context (or whoever I heard this from is mistaken).

By using the second (storing each element as a separate row in the relevant table), I would have a database structure like this:

surveys

id name

1  Survey

sections

id name         survey_id index

1  Introduction 1         0

pages

id text                           section_id index

1  Please answer these questions. 1          0

questions

id text                    page_id index

1  Are you male or female? 1       0

answers

id text   question_id index

1  Male   1           0
2  Female 1           1

The benefits I see to this are:

  1. There is a better separation of content, and
  2. It's easier to edit outside of the script (like when using Navicat, for example)

The main drawback I see to this is:

  1. It's more difficult to edit inside the script (many queries with complicated logic to deal with re-ordered elements or re-structured answers)

My question to you is this: which of these two methods (if indeed either) is better suited to the task-at-hand? I'm tempted to side with storing the data as a serialized array as it seems a lot simpler to build and easier to control. Although I can see the ... elegance ... in storing the data as separate rows in relevant tables.

If it's of any relevance, at the moment the survey is built using JavaScript: HTML elements are added, removed, and re-ordered using (specifically) jQuery and then the finished product is submitted in a form (with the $_POST array being the example I gave above).

Upvotes: 1

Views: 414

Answers (5)

Avyakt
Avyakt

Reputation: 313

In my opinion, the most comfortable way to storage data is in database, for following reasons:

  1. cross-platform format;
  2. SQL is well known standard;
  3. PHP + SQL is a very good combination;
  4. SQL database has statistics;
  5. Good support in the form of literature, etc...

Upvotes: -1

fourreux
fourreux

Reputation: 125

The questions I would ask myself is:

Will I ever have another use for that data than the one I have in mind right now? As pointed out already You could use the data for statistical purposes.

Or: Will anyone else?!

I would go for a serialized storage only in the case that I'm building some sort of "data dead end", i.e. I know that data will rarely/never be edited again.

Or I would do serialized storage if I know that data will always be accessed as a whole and there's very little use in accessing parts of the entry.

my 2 cents.

Upvotes: 1

Pavel Strakhov
Pavel Strakhov

Reputation: 40512

Storing in database is better than storing in serialized form. Also, consider storing data in configuration files that can be easily read and edited outside of the script (json, yaml, ini formats). There are simple methods for reading and writing these formats in PHP.

Upvotes: 0

Billy Moon
Billy Moon

Reputation: 58619

I would say that option 1 is more adaptable, and option 2 is more efficient, so the choice is between efficiency and adaptability. Efficiency is achieved by database passing variables, rather then extra step of parsing serialized strings. Adaptability, because the data structure could be different for different surveys for example, without actually modifying the database. Also, the data types do not need to be defined.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838994

Storing your data in normalized form is probably the way to go here. When you collect answers from your users, it's typically because you want to perform some analysis on the results.

By storing in normalized form it is easy for example to see what percentage of responders where male/female.

SELECT text, COUNT(*) AS count
FROM answers
WHERE question_id = 1
GROUP BY text

Upvotes: 3

Related Questions