What do you think is the better way to go about storing a few image id's inside a record in a MySQL database? It's just the image id's which will be used to fetch the images from a different library.
Do i implode the id's in the record like 1#4#7#9#10#12 or do I just serialize the array and store that? Are there any performance benefits by using the one instead of the other? Stability preferences?
I have just always used implode and explode, never really gave it much thought. Thanks.
I would pefer serialize or JSON-encode.
It is more flexible and for example will allow you to add image title and other details there in future...
If you don't want to (over?)normalize your tables, and you really just want to store a list of ids then I suggest using a simple comma-separated list, because already MySQL has some functions which can directly deal with comma-separated string values:
FIND_IN_SET:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set
SELECT FIND_IN_SET('b','a,b,c,d'); --> 2
CONCAT_WS: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws
SELECT CONCAT_WS(',','First name',NULL,'Last Name'); --> 'First name,Last Name'
Of course, you won't be able to do SQL JOINs, but it still can be helpful.
As far as I know there are not significant differences in this case but implode() is a bit faster since it assumes an array and serialize() does not know what you are passing to it.
EDIT based on OP's comment:
Well all the images are stored in a seperate library table with the title and descriptions and things like that. But yeah I see your point.
In that case is not a good idea so serialize several IDs into a single field. What you need is a *-to-Many relation between your 2 tables. This is the correct way of represent multivalued fields:
+----------------------+
| USER |
+---------+------+-----+
| user_id | name | ... |
+---------+------+-----+
+----------------------+
| USER_PICTURE |
+---------+------------+
| user_id | picture_id |
+---------+------------+
+--------------------------+
| PICTURE |
+------------+-------+-----+
| picture_id | title | ... |
+------------+-------+-----+
My friend, serialization is to obtain a string representation of an object's status. Even if it works i don't think is the best way to do what you want. I would prefer to store a json object with the ids. Because a json object is multiplatform, is a string and is easily readable by a human i think is a good approach.
