Quickest way to represent array in mysql for retrieval

Question

I have an array of php objects that I want to store into a mysql database table. The only way I can think of is just have a table to represent the object with a unique id and a separate table to store the array (there could be a column array_id and an object_id) but retrieving would require a join I believe which could get expensive. Is there a better way? I don't care much about storage space or insertion time as much as retrieval time.

I don't necessarily need this to work for associative arrays but if the solution could, that would be preferred.

Answer 1

If you are comfortable with not being able to SQL search through the data within the array, you could add a single column to the table, and serialize the array into it. You would have to deserialize it on retreival.

You could use JSON / PHP serializeation or whatever is more appropriate for the language you're developing in.

Answer 2

Joins don't have to be so expensive - you can define an index.

Answer 3

Building a tree structure (read as Array) in mysql can be tricky but it is done all of the time. Almost any forum with nested threads has some mechanism to store a tree structure. As another poster said they do not have to be expensive.

The real question is how you want to use the data. If you need to be able to add/remove data fields from individual nodes in the tree then you can use one of two models

1) Adjacency List Model

2) Modified Preorder Tree Traversal Algorithm

(They sound scary, but it's not that bad I promise.)

The first one listed is probably the more common you will encounter and the second is the one I have begun to use more frequently and has some nice benefits once you wrap your head around it. Take a look at this page--it has an EXCELLENT writeup about both. http://articles.sitepoint.com/article/hierarchical-data-database

As another poster said though, if you don't need to change the data with queries or search inside the text then use a PHP function to store it in a single field.

$array = array('something'=>'fun', 'nothing'=>'to do')

$storage_array = serialize($array);

//INSERT INTO DB

//DRAW OUT OF DB

$array = unserialize($row['stored_array']);

Presto-changeo, that one is easy.