9

I am currently working in a product where different types of images like product images, user profile pictures, logo etc. are there. I need a database with good query performance.

I got two DB designs in mind.

OPTION 1. - Storing all images in a single table with id,title, url_full, url_thumb , status and timestamp field

Advantages

  1. I can use single ImageModel file to insert delete / update data. So there will be no multiple logic for image storage. It is just a single logic, "storing in a single table". So whenever image has to be saved, I can call the method of ImageModel

Disadvantages

  1. If there are lot of product images and less user images, the user image querying will become slow due to the huge number of products.

OPTION 2. - Storing different type of images in different tables with id,title, url_full, url_thumb , status and timestamp field

Advantages

  1. Increased number of records in one section won't affect the query speed of other

Disadvantages

  1. Has to write separate model files / functions for each image type.
  2. Whenever image has to be stored, type needs to be specified.

My question is , which is the better approach. Is the advantages and disadvantages a real concern.Also if there are any other advantages / disadvantages, please list. Or if there are any other god db designs, please suggest.

Please answer based on the practical scenario where there are lots of products and users.

Jinu Joseph Daniel
  • 4,856
  • 12
  • 53
  • 87
  • "good query performance" -- Please show us the queries; without them, we cannot help you. – Rick James Sep 26 '16 at 23:38
  • 1
    What is a typical size for the images? If we are talking kilobytes, then one answer is better; megabytes then another is better. – Rick James Sep 26 '16 at 23:40

2 Answers2

8

This began as a long comment so I decided to post it as an answer. Storing different types of images in different tables sounds like a bad idea to me. For one thing, how will that design scale if, for example, new types of categories appear later? Would you then be able to cope with adding an arbitrary number of new image tables? Also, querying all images would require either a series of joins or unions, which could be costly.

You mentioned the following advantage to a multi image table schema:

Increased number of records in one section won't affect the query speed of other

If you use a single image table with an index on the type column, then increasing the number of records of one type won't necessarily increase querying for images of a second type. And here is a disadvantage to a single image table which you gave:

If there are lot of product images and less user images, the user image querying will become slow due to the huge number of products.

It is true that adding more records will generally slow down querying. However, having an appropriate index on the type should greatly diminish this problem.

A single image table with appropriate indices seems much better.

Tim Biegeleisen
  • 387,723
  • 20
  • 200
  • 263
  • Thank you. Actually I got confused from suggestions from other architects in the team.. Hope this is possibly the best structure – Jinu Joseph Daniel Sep 24 '16 at 08:21
  • 1
    One scenario where storing the images in sepearate tables might make sense would be if the table is just plain too large, e.g. millions of records or more. But even then, you'd just be sharding the single table model. – Tim Biegeleisen Sep 24 '16 at 08:22
  • Our application is targetting 2 million users...and there will be around 75000 products initially which may scale up with time.. – Jinu Joseph Daniel Sep 24 '16 at 08:24
  • 1
    75K products doesn't scare me, at all, nor does 750K. If you get into the millions and performance degrades, then maybe it's time to rethink things. – Tim Biegeleisen Sep 24 '16 at 08:26
  • And do you know how much is the percentage decrease in query speed per 10000 records. Also is it exponential decrease?? – Jinu Joseph Daniel Sep 24 '16 at 08:27
  • This totally depends on the table, database, traffic, number of users, etc. – Tim Biegeleisen Sep 24 '16 at 08:28
-1

How will you deliver the images? If it is via <img src=http:...> then there is only one answer: separate files.

Yes, thumbnails can be done by other mechanisms, such as converting to base64 and including in the img tag. This might be best if you have a lot of thumbnails on the page.

But having the thumbnails in the same table as the columns you query on could be bad for performance. So we need to see the queries and the table schema (as it stands so far).

Rick James
  • 106,233
  • 9
  • 103
  • 171