Arrays vs Tables (PHP/MySQL)

Question

Meet Jimmy. He has made his new life goal to prove that Chocolate is the best ice cream flavor ever. For this he built a simple form with radio buttons and a text field for the name so he can send the link to his friends.

He is using a very common set up, MySQL and PHP to save the form submissions in a table that looks like this:

selection being the id of the flavor. The flavors are stored in a PHP array because he plans to use the favor list in future pages:

$flavors = array(    1=>"Chocolate",    2=>"Cherry",    .... ); 

The form was a success and his friends are starting to ask Jimmy to add new options, so Jimmy has decided to take it to the next level and add country, age, email and other things to the form, but this time he is doubtful about whether it is a better idea to put the flavor names, countries, ages and other static data in arrays or save each of them in a database table, he knows how to do joins in queries anyways.

First approach

would mean having a PHP file with many arrays and having access it every time Jimmy needs the flavor name something like:

$query = mysql_query("SELECT name, flavor FROM votes") while($row = mysql_fetch_assoc($query)){    echo $row["name"]." - ".$flavors[$row["flavor"]]; } 

Second approach

would mean having many tables in the database and having to do a join every time he needs a name like this:

$query = mysql_query("SELECT name, flavor FROM votes LEFT JOIN flavors  WHERE votes.flavor = flavors.flavor");  while($row = mysql_fetch_assoc($query)){    echo $row["name"]." - ".$row["flavor"]; } 

Although there seems to be little difference this is an important decision for Jimmy as he wants to build many more and bigger forms in the future.

What is the best way for Jimmy to handle static data like flavor names, countries, age groups, etc. that is associated with IDs in the database?

Given environmental details:

1. The arrays are static and will almost never change
2. He will be using the data on several pages so hard coding is not convenient
3. Adding a new array is usually faster

Thanks in advance for helping him out.

Answer 1

The second option is of-course more scale-able and almost better in every aspect, the only argument could be performance given that his data is gonna eventually get really big. But even at that point jimmy can easily cache the result from the new flavors table, using a technology like memcache, x-cache, or even write a code that will create the php file with the array of flavors dynamically using the flavores database. I am very confused as why someone with your reputation will ask such a question?!

Answer 2

I think Jimmy should be thinking about how he wants to lay his data out. Why limit his conquest to flavors.

If Jimmy is looking to store a lot of small bits of data, databases are the way to go. If Jimmy wants to store images of the items, those should be stored in files and he should store their relative location to some root directory in the database

Maybe one table can contain:

VOTE_ITEMS     ID - PRIMARY KEY     NAME     IMAGE     TAGS - (Maybe an imploded ID array with the IDs pointing to a TAG table)     ... 

Another table can contain:

USERS     ID - PRIMARY KEY     ...     (As much information as your want to collect from your users)     ... 

On to voting:

POLLS     ID     VOTE_ITEM_IDS     ...  USER_VOTES     POLLS_ID     VOTE_ITEM     USER_ID 

Since Jimmy seems to know a lot about databases, anytime he wants to add something on he can just add another column (or table) depending on his needs. Also, if we wraps a sweet user system he can reuse it in other projects in the future!