12

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?

2 Answers 2

9

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.

9

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.

4
  • +1, even without a CDN, why would you want to destroy your DB's performance with long image reads? DBs are the hardest application elements to scale. Apr 17, 2017 at 17:59
  • 1
    I don't see how using BLOB prevents me from using caching CDN or other tools listed. DB can be used as source of truth, while other components are just various caches, which could not care less about original storage.
    – Basilevs
    Apr 17, 2017 at 18:10
  • 2
    Non relational data (images) do not belong in relational databases. Argue all you want from a conceptual point of view, but your application performance will suffer. Apr 17, 2017 at 18:21
  • 1
    @Basilevs Modern object stores like S3 are just a better soluiont to the problem, but before they were mature BLOBS were the only option... Replication of objects is faster & more stable given lower complexity vs ACID compliant SQL transactions & native cache server http protocol support adds more advantages... Varnish is an intermediary origin in my eample above effectively pulling objects out of the stream to cache them in RAM, eliminating file system latency & lock contention delays that pthreads can introduce. Apr 19, 2017 at 3:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.