PHP : To serialize or not
I have a products table containing products. Each product can have multiple images. The urls/id of the images are to be stored in my database. Now, instead of creating columns such as image_url1, image_url2 , etc. in my products table, is it good practice to store the serialized data containing the list of images in my products table in one column called image_url.
Or is it better to create another product_image relationship table and store the product ids and image ids/urls there?
Am sure both methods will work, but I want to go with the best practice.
Note: The number of images per product will vary. Some products may not even have an image.
Edit: To add more specific detail to my question. The image URLs will not be used for searching. It will be used just to retrieve the URL of the image when the product is being displayed.
Best practice would be to use a separate table. Serialized data into a single field would work, but it's not a good idea.
What if you need to access the database with a different language. Would it be able to deserialize a PHP serialized object? (no)
Using serialized data would require a lot more code.
Delete an image example
delete from product_images where product_image_id = 4
- Get serialized data from database.
- Deserialize into array or object.
- Loop through the data to find the one you need.
- Remove it from the list.
- Serialize the modified list and save it back to the database.
A lot of people will tell you that it kills the point of a relationship database engine (or whatever) if you store a list of things in one field.
However, if you never have any need to search by that field (in your case, if you never need to take an image url and find the product that owns the image), or the field is never subject to constraints (for example if there's no need to ensure the same image isn't used twice), you've got no reason not to just dump data into a single field.
Best practices aren't silver bullets, they're always best fits for particular situations, be they broad or narrow.
If all you're going to do is loop over the list of images I don't see the harm. If however you're going to be editing or searching for particular images I wouldn't do it. Instead of your other choice (named image fields), why not put images in a different table with a foreign key to the product they belong to.
Alternatively, if you really need to stuff all of them into one field, you might look into your db's XML capabilities. I've done some stuff with encoding lots of fields in an xml document and storing it as a field in the db, then using XPath to search through it. You're dependent on the level of XPath functions implemented by the db vendor but in my case it was quite performant and allows us to store different types of the same class of object in the same table.