Is storing image paths in a database necessary?

Question

First, I noticed there are many questions regarding this, lots marked as duplicate.

I eventually came to this one.

And the accepted answer for that question although partially solves my question, it doesn't answer all of it.

My question is, a user uploads an image. I store the path in the database and the image file in the file system. However, I make 3 copies of that image (large-, medium- and small-sized). So all in all I have 4 images - original, large, medium, small.

Should I store all 4 paths in the database, like so

ID  |      original      |    large        |    medium       |    small       |
----+--------------------+-----------------+-----------------+----------------+
 1  |  /path/to/original | /path/to/large/ | /path/to/medium | /path/to/small |

or just store the original's path, and give the other 3 a naming convention, like so: car.jpg, car.jpg, large-car.jpg, medium-car.jpg, small-car.jpg?

I feel this way would be less heavy on the database, and that if later, I wanted to add another size (ie. extra small) I wouldn't have to moditfy the database.

Answer 1

If all the images in a given row live in the same place, I'd say their base path should be its own column (rather than re-deriving the base path from the original image's full path all the time).

If all the images in the database live in the same place, don't store the base path in this table at all; have it in code, or in a global configuration table.

Answer 2

For sure have a solid naming convention for the various sizes of the original image, this will help you with generating known cache keys so you can store the images in some cache, like memcache, this relieves the load on the db and server's disk i/o

Answer 3

Seems like you are trying to overuse the Database. How about this method instead.

ImageID  | UserID  | name.. 
---------+---------+-----
1        | 495     | car454.jpg
2        | 495     | house.jpg
3        | 44      | kittysmall.jpg

And Store all the images in one place.

IMAGES_PATH = "/path/to/images"

And name the images by the imageID (Auto Increment), so for the 5th image, it would be 5.ori.jpg or 5.large.jpg etc

This way you can easily see who owns what image, and also the user can upload different images with the same filename and not have to worry about that.

Answer 4

To generalise, I'd say if you can recreate the information (because the base is always the same, followed by the users name), don't store it in the database. If you later want to change the directory where you store images for whatever reason, you'd be in trouble.

Answer 5

As a minor tweak, I'd be tempted to place the generated thumbnails, etc. in a different path (e.g.: ../generated/) to ensure that you don't overwrite the source image if someone uploads a file called 'car-large.jpg', etc.

Answer 6

If the specific paths are consistent except for the file names why not use constants for the paths and then just store the different sized images in the appropriate directories and reference just the file names in the database.

The main principle here is avoiding duplicate information, in the database and in your code. For the database you achieve higher normal form, and for the code you achieve DRY (Don't Repeat Yourself).

Assume you structure is something like

/home/user/site/images/original/

/home/user/site/images/small/

/home/user/site/images/medium/

/home/user/site/images/large/

you could use constants for that info. e.g.

PATH_ORIGINAL = /home/user/site/images/original/

PATH_SMALL = /home/user/site/images/small/

PATH_MEDIUM = /home/user/site/images/medium/

PATH_LARGE = /home/user/site/images/large/

Then in your code you could do something like

smallcar = PATH_TO_SMALL . car.jpg;

Or just insert the appropriate constant variable inside whatever query output you have for loading the images.

The added benefit is that you have one place to change paths if you need to tweak directory structures or move code between servers, rather than update a whole slough of database records, which might be more problematic and error prone.