Creating a separate table for images or adding "image fields" to many tables?

Question

I need to create a database in which several tables have images.

For example, users have profile pictures and uploaded ones and products have many pictures, as well.

Is it better to have one SQL table "images" or add "images" to every table that contains them?

Also, how would you add many images into one column?

I've read about the advantages of storing the file paths vs the actual files in the database but what about a long string with many file paths vs many rows?

For example

INSERT INTO images (userid, image1, image2, image3)
VALUES (autoincrementedPK 'pathtofile1', 'pathtofile2', 'pathtofile3')

or

INSERT INTO images (userid, images)
VALUES (autoincrementedPK, 'pathtofile1, pathtofile2, pathtofileN')

Which option is better and why? Is option two viable? and if so, how would you implement it?

Answer 1

First about storing file paths:

• Having several image path columns (imagepath1, imagepath2 ... imagepathN ) violates 1NF.
• Storing several comma-separated image paths in the same column also violates 1NF.
• When you violate the simplest of normal forms, you will have a lot of headaches in the future.
• The correct thing to do is create a separate table for images. That table would have a FK with the user table (userid). You then insert into that table as many image paths as you want to any given user.

Regarding storing actual images as BLOBS.

• Same rules about NF1 apply. Same separate table for images applies.
• In this case having separate tables for BLOBS is good because the DBA can put the image tables in a separate tablespace, optimizing the disk IO.
• Even in the case users are allowed only one image, denormalization by creating a separate table with a one-to-one relationship is recommended.

Note: I can't give you syntax because I'm not familiar with MySQL datatypes.

Answer 2

I agree wholeheartedly with Tulains Córdova on the question of file paths, but can't disagree strongly enough about BLOBS.

Although the question doesn't specifically mention if it's a web application or a more traditional client-server application, it sounds like we're talking about a web app from the context provided about USERS & PRODUCTS.

If that's the case, then there is no excuse not to be using a CDN, especially with amazing free transparent CDN's like cloudflare. There are others, but I love CloudFlare and can't recommend it enough. I suppose it matters more that you use a CDN at all than which particular one you prefer...

Storing images in the DB as BLOBS has never really been faster than the file system unless you have specialized hardware which you can tune at a very low level to optimize file IO (and there are few experts I've run across who really do this better than the thousands of engineers working on this problem at the OS / driver level).

Even worse, by using DB BLOBS, you not only preclude the incredibly faster CDN option of geographically distributing and hosting static image (and CSS, and JS, and fonts...) assets as close to your web visitors as possible, but you also wind up giving up the option of using faster web servers like nginx or front-end caching systems like Varnish to serve up your images (and CSS, JS, etc...) which is a shame because they are WAY faster than apache and IIS if properly configured.

Obviously, you'd also give up the option of combining nginx, varnish, and a CDN since you can't use the individual components unless the assets are stored on the file system.

I've developed some apps that were being deployed onto clustered cloud environments which created a challenge of distributing file assets that were needed by every server in a cluster. Initially you might be tempted to consider storing image assets in the DB as BLOBS so every node could access them and then you don't need to worry about deploying those assets...

Avoid this temptation. I've found it's better to let file systems, web servers, caches, and CDN's do what they do BEST and leave the DB to do what it does best. And that's definitely not storing images and serving them quickly to web applications.