Best way to store an array in MySQL database?

Question

Part of my app includes volume automation for songs.

The volume automation is described in the following format:

[[0,50],[20,62],[48,92]]  

I consider each item in this array a 'data point' with the first value containing the position in the song and the second value containing the volume on a scale of 0-100.

I then take these values and perform a function client-side to interpolate this data with 'virtual' data points in order to create a bezier curve allowing smooth volume transition as an audio file is playing.

However, the need has arisen to allow a user to save this automation into the database for recall at a later date.

The datapoints can be unlimited (though in reality should never really exceed around 40-50 with most being less than 10)

Also how should I handle the data? Should it be stored as is, in a text field? Or should I process it in some way beforehand for optimum results?

What data type would be best to use in MySQL to store an array?

Answer 1

Definitely not a text field, but a varchar -- perhaps. I wouldn't recommend parsing the results and storing them in individual columns unless you want to take advantage of that data in database sense -- statistics etc.

If you never see yourself asking "What is the average volume that users use?" then don't bother parsing it.

To figure out how to store this data ask yourself "How will i use it later?" If you will obtain the array and need to utilize it with PHP you can use serialize function. If you will use the values in JavaScript then JSON encoding will probably be best for you (plus many languages know how to decode it)

Good luck!

Answer 2

I suggest you to take a look at the JSON data type. This way you can store your array in a more efficient way than text or varchar, and you can access your data directly form MySQL without having to parse the whole thing.

Take a look at this link : https://dev.mysql.com/doc/refman/5.7/en/json.html