Performance of implementing product images in an e-commerce store as a separate table vs. as columns of the Product table?

Question

I am creating an e-commerce store in Django and am currently trying to decide between implementing product images as an entity of their own then linking to the product by a foreign key or putting image fields as attributes in the product entity.

In the answers I've seen to this question, most people recommend implementing a separate table for images and I would prefer doing this because then, I will not have a limit to the number of images I can add to a product.

My concern however is, will this come at a huge speed cost for the customers using my website i.e. for my product listing page, I would have to, for each product, filter (query) the images corresponding to the particular product from a table of several images. I assume it would be faster if I make the images columns in the actual product table.

I would give a number of rows in the images table but, I have no idea how many images I will wind up having for the store.

Answer 1

If those images will be delivered to web pages, it is better to store the URL (or the key part of the URL) in the database table. Have the image in a file. That way, the HTML <img src=...> can be generated by your code and very easily be fetched by the user's browser.

The limit, of course, is disk space where you store the images. And there is no need to have the images on the same server. Nor do all the images need to be in a single server.

Building the web page does not have to touch the image. That is handled asynchronously later.

Most image formats (jpg, etc) are already compressed. It is wasteful to compress them again.

Answer 2

“ I will not have a limit to the number of images I can add to a product.” Then you need a new table or to store all images in one row ( zipping could work).

You’re going to index this table on the lookup key so any additional overhead is going to be small. Remember that images are going to be large objects and unlikely to be stored in the same page as the other data anyway so there’ll be additional lookups for both options either way.