Separate Table for Images vs Adding Image Fields in Database Design

blobdatabasedatabase-designimageMySQL

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?

Best Answer

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.

Related Topic