Databases
StackOverflow2013
Postsdbo
POTo serialize array or not to serialize array: how to store a survey
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTo serialize array or not to serialize array: how to store a survey
    primarykey
    data
    text
    <p>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:</p> <ol> <li>To use a serialized array, or</li> <li>To store each element as a separate row in the relevant table</li> </ol> <p>For example, by using the first I would store something like the following as a serialized array:</p> <pre><code>Array ( [name] =&gt; Survey [sections] =&gt; Array ( [0] =&gt; Array ( [name] =&gt; Introduction [pages] =&gt; Array ( [0] =&gt; Array ( [text] =&gt; Please answer the following questions. [questions] =&gt; Array ( [0] =&gt; Array ( [text] =&gt; Are you male or female? [answers] =&gt; Array ( [0] =&gt; Male [1] =&gt; Female ) ) ) ) ) ) ) ) </code></pre> <p>The benefits I see to this are:</p> <ol> <li>It is centralized, and</li> <li><p>It is easy to edit within the script using a simple query like:</p> <pre><code>UPDATE surveys SET data = '$serialized_array' </code></pre></li> </ol> <p>The main drawback I see to this is:</p> <ol> <li>It is difficult to edit outside the script (like when using Navicat, for example)</li> </ol> <p>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).</p> <p>By using the second (storing each element as a separate row in the relevant table), I would have a database structure like this:</p> <p><strong>surveys</strong></p> <pre><code>id name 1 Survey </code></pre> <p><strong>sections</strong></p> <pre><code>id name survey_id index 1 Introduction 1 0 </code></pre> <p><strong>pages</strong></p> <pre><code>id text section_id index 1 Please answer these questions. 1 0 </code></pre> <p><strong>questions</strong></p> <pre><code>id text page_id index 1 Are you male or female? 1 0 </code></pre> <p><strong>answers</strong></p> <pre><code>id text question_id index 1 Male 1 0 2 Female 1 1 </code></pre> <p>The benefits I see to this are:</p> <ol> <li>There is a better separation of content, and</li> <li>It's easier to edit outside of the script (like when using Navicat, for example)</li> </ol> <p>The main drawback I see to this is:</p> <ol> <li>It's more difficult to edit inside the script (many queries with complicated logic to deal with re-ordered elements or re-structured answers)</li> </ol> <p><strong>My question to you is this:</strong> 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 ... <em>elegance</em> ... in storing the data as separate rows in relevant tables.</p> <p>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 <code>$_POST</code> array being the example I gave above).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USMichaelRushton
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTo serialize array or not to serialize array: how to store a survey
      1. USMichaelRushton
      1. VTFavorite
    1. COThe question might be moot if you're willing to consider a different database, like a document store such as MongoDB.
      singulars
      1. POTo serialize array or not to serialize array: how to store a survey
      1. UScmbuckley
    2. COThat looks interesting. Although, I'm not building this survey system solely for my own use but for anyone and everyone to download and implement themselves, so it would perhaps be best for it to not rely on less commonly-used 3rd-party software
      singulars
      1. POTo serialize array or not to serialize array: how to store a survey
      1. USMichaelRushton
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload