Should I store arrays in custom fields or create custom tables?

Question

I am developing a website where I have modified the comment form to have review rating capabilities. I ask them to rate them on food, service and location. For each of those option there is “bad”(1),”okay”(2),”Good”(3). I store each in a custom comment field like the following “review_service”=1 & “review_food”=3.

Right now when a page loads “get_comments” is used to get all the custom comment fields and count them in the following array. The array is used to display results.

Array
(
[food] => Array
    (
        [1] => 3
        [2] => 1
        [3] => 1
    )

[service] => Array
    (
        [1] => 1
        [2] => 2
        [3] => 2
    )

[location] => Array
    (
        [1] => 1
        [2] => 3
        [3] => 1
    )

)

In future I want to show locations listed in order of counts. I’m sure using ‘get_comments’ through all posts is not efficient. I thought of two ideas.

1. Store the array (serialized) in a post custom field for the location. Each time a review is done the array in the custom field is updated.

2. Put the array in a custom table like below. EDIT: I forgot to add the option column.

   |Id | post_id | option_id |option|option_count|

   |1 |1433 |food |bad |5 |

   |2 |1433 |food |good |3 |

   In terms of speed/efficiency which option should I choose?

Answer 1

Since you plan on retrieving and displaying the data ( and most likely wanting to sort it ) then you should store your reviews data in a custom table.

All of these fields could be set as integer only: id,comment_id,food,service,location

Then you could grab the data by joining this table to the comments table and the comments table to the posts table.

Answer 2

Storing data in a serialized array a is terrible way to store data unless all you want is to pulling out in a block. If you want to query the data contained in the serialized string you've got a mess. Don't do that. Store granular data.

Second, your "custom table" really looks like the commentsmeta table or the postmetatable. It makes not sense to duplicate that. WordPress does not have a Core function that I am aware of that will pull metadata by key alone and not tied to an ID (which is a limitation in Core) so you will have to write your own function to pull that data, but you would anyway.

See also:
https://wordpress.stackexchange.com/a/82585/21376
https://wordpress.stackexchange.com/a/88715/21376