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
Disadvantages
OPTION 2. - Storing different type of images in different tables with id,title, url_full, url_thumb , status and timestamp field
Advantages
Disadvantages
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.
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.