Optimal Way to Store/Retrieve Array in Table

Question

I currently have a table in MySQL that stores values normally, but I want to add a field to that table that stores an array of values, such as cities. Should I simply store that array as a CSV? Each row will need it's own array, so I feel uneasy about making a new table and inserting 2-5 rows for each row inserted in the previous table.

I feel like this situation should have a name, I just can't think of it :)

Edit

number of elements - 2-5 (a selection from a dynamic list of cities, the array references the list, which is a table)

This field would not need to be searchable, simply retrieved alongside other data.

Answer 1

The "right" way would be to have another table that holds each value but since you don't want to go that route a delimited list should work. Just make sure that you pick a delimiter that won't show up in the data. You can also store the data as XML depending on how you plan on interacting with the data this may be a better route.

Answer 2

Maybe the word you're looking for is "normalize". As in, move the array to a separate table, linked to the first by means of a key. This offers several advantages:

• The array size can grow almost indefinitely

• Efficient storage

• Ability to search for values in the array without having to use "like"

Of course, the decision of whether to normalize this data depends on many factors that you haven't mentioned, like the number of elements, whether or not the number is fixed, whether the elements need to be searchable, etc.

Answer 3

I would go with the idea of a field containing your comma (or other logical delimiter) separated values. Just make sure that your field is going to be big enough to hold your maximum array size. Then when you pull the field out, it should be easy to perform an explode() on the long string using your delimiter, which will then immediately populate your array in the code.

Answer 4

Is your application PHP? It might be worth investigating the functions serialize and unserialize.

These two functions allow you to easily store an array in the database, then recreate that array at a later time.

Answer 5

As others have mentioned, another table is the proper way to go.

But if you really don't want to do that(?), assuming you're using PHP with MySQL, why not use the serialize() and store a serialized value?