Store images on database or filesystem?

[eluser]josephfley[/eluser]
Hi,

This question isn't really about CI, but web programming in general.

So how do you store images in your applications and why?

I have allways keeped them on filesystem, but the option to forget about he logic to keep the fs<->db in sync is tempting.

[eluser]davidbehler[/eluser]
I have never done that before, but google had some links for me on that subject:

http://www.codewalkers.com/c/a/Database-...-Database/
http://www.oracle.com/technology/product...abase.html
http://blog.sqlauthority.com/2007/12/13/...mages-url/

[eluser]bitist[/eluser]
I would never store images in database.

[eluser]josephfley[/eluser]
[quote author="devpedia" date="1233269055"]I would never store images in database.[/quote]

Care to share some reasons? As said I have never done it before, I'm just trying to investigate more on the subject.

I mean for example you have a users table and want keep a 50x50 avatar for each one, wouldn't be using db for storage a viable option?

[eluser]The Wizard[/eluser]
what will you do with the pictures?
Search'em ?

Its absolute unnecessary to store them in the database
better store them in the filesystem and access them there.

By the way, i do it like this:
i store the filename, if i want a thumb or a mini of the picture
(which i generate when uploading) i add the name tag in front
of the filename i get from the db like for example: test.jpg

i do, thumb_ + test.jpg and i have the thumg, i add mini i get the
mini, i add nothing, i get the original

[eluser]slowgary[/eluser]
This is a little late, but I have a similar question and this seems like a good post to piggy back on ;-P

I plan to store product images on the file system as the database seems unnecessary, but each product will likely have multiple images. So how do I handle image retrieval? I see 2 methods, but I'm not sure which one is better or if I'm missing any others.

1. Create a `product_images` table and store each filename and the id of the product it's associated with

2. Name the images with a prefix of the product id or sku, and do a directory scan, iterating through throut the array for names that begin with the current product's id or sku.


I'm leaning towards method 2 because I won't need to use another db table for files that will not be stored in the database, but it seems like doing a directory scan through potentially thousands of images then having to iterate through the returned array would be too expensive.

Any ideas?

[eluser]Mushex Antaranian[/eluser]
As I get you, you don't want to keep another table in db for assigning images to your product, and think (truely ) that it will require more resources with scanning directory for filenames with certain prefix.
Then you can simply create new directory for each product (lets say named by product id) and store images there. It will not let you connect an image with 2 or more product, but the same problem you'll have with prefixes.

P.S. I can't say anything about performance, I'm connecting files to items with db records, but looks like it will faster than via filtering thousands of images by prefix.

[eluser]slowgary[/eluser]
Thanks Mushex.

I definitely think the database method would be faster, I've just always tried to avoid unnecessary database tables and in this case I'll still have to read the files from disk, so if I can avoid doing a query beforehand that would be bonus.

What about naming my files with a product id or sku as the prefix, then something sequential, e.g.

Code:

JSE54736_IMAGE_001
JSE54736_IMAGE_002
JSE54736_IMAGE_003
JSE54736_IMAGE_004

Then I can just assume 001 and increment until the file read fails. Of course this would require more code to add or remove images. I'm just thinking out loud here (and typing it). I think having a separate table makes most sense after all. Any other suggestions?

[eluser]josephfley[/eluser]
Hi,

I was in the same situation and ended going with your option 1 (creating a separate table), mostly because with solution number 2 it could be problematic if you upload some pictures and then delete some of them (leaving holes in the sequence). I know there are some ways to still make it work, but I just didn't feel they were the right way to do it.

I'm not really satisfied with option 1, but looks like the best way right now.

[eluser]Tom Schlick[/eluser]
[quote author="slowgary" date="1242985447"]Thanks Mushex.

I definitely think the database method would be faster, I've just always tried to avoid unnecessary database tables and in this case I'll still have to read the files from disk, so if I can avoid doing a query beforehand that would be bonus.

What about naming my files with a product id or sku as the prefix, then something sequential, e.g.

Code:

JSE54736_IMAGE_001
JSE54736_IMAGE_002
JSE54736_IMAGE_003
JSE54736_IMAGE_004

Then I can just assume 001 and increment until the file read fails. Of course this would require more code to add or remove images. I'm just thinking out loud here (and typing it). I think having a separate table makes most sense after all. Any other suggestions?[/quote]

or you just have a table in your db called lets say 'product_images' along with another table called 'products' that contains an ID of the product. you then (when you are adding images to the db) add a product_id field to the product_images table so with one query you can say "SELECT * FROM `product_images` WHERE `product_id` = '456'" and bam you have all you need

i dont know why everyone is so afraid of using a db table to store image names. it would be equal speed of scanning an entire folder structure for a certain prefix on a filename. so why not save yourself the hassle and just use a db query that you know will return the right info. unless your db server is housed behind an AOL connection you are good on the speed.