Serialize vs Implode

Question

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.

Borkowski

The best solution is to normalize your tables – Mahaliamahan

Definitely a case for normalising the tables and having a separate image list table. – Columbine

For your use case implode is better than both JSON and serialize simply because not only it's faster but also because you don't need to store keys, storing keys in your case is wasteful. – Yellows

Answer 1

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...

Damron

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. – Borkowski

@nickcorin In that case you shouldn't serialize several IDs into a single field. What you need is a *-to-Many relation between both tables. – Ravenous

@BogdanBurim or he just didn't know, maybe. – Ravenous

@jackflash I think he does, as he guessed to put titles into separate table – Damron

I just prefer using the id's guys.. It's a personal preference of mine. But I think I'm going with JSON, thanks for the help though – Borkowski

@BogdanBurim That proves he does know he needs one table for his pictures and another for XXX but perhaps he don't know whats the right way to relation them. But I don't know... :) – Ravenous

I think it would be easy to use functions like find_in_set if I store in a comma separated list instead. – Singlehanded

Answer 2

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.

Turco

Yeah well if I'm seperating, that is pretty much just using implode? – Borkowski

Yes, it is perfectly the same, but it might be one reason to keep your existing implode solution (just change # to comma). – Turco

Answer 3

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 | ... |
+------------+-------+-----+

Ravenous

Answer 4

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.

Stew

Yeah this could be the best way to go about it, because I need to create an API to connect to an Android and iOS app. I will be using JSON for that so its probably the best way. Thanks! – Borkowski