images in database or in folder ?

loic_phpbuilder

Hello

I'm a newbie at php and mysql.

I want to create a database for a photographer.

What is best ?
- upload the images in the database (as 'blobs' as I understand it)
- or upload the images in a directory and store the path and name into the database

What I have to consider is :
- ease to upload, alter and potentially delete images
- thumbnails (do we upload them or do we create them on the server ?)
- potential (remote) need to transfer database in another environment (even other DBMS... is it possible ?)

Thank you

Loic

Pig

It is significantly easier and faster to store the images in folders and the path in the database. Plus, imagine the difficulty and time involved in trying to back up/restore a database with 50 MB of images, vs 200 KB of links.

AstroTeg

Blobs are not created equal in all DMBSs. Blobs have issues in MS SQL and I've seen documentation for MySQL showing they have some issues and limitations as well (from what I remember - I could be wrong).

By storing the image in a database, you're storing a large chunk of data that you won't be doing much with besides retrieving (its not like you'll be searching that data or sorting by that data).

By storing the link in the database and the image to the file system, you let the database do what it does best - manage information and the file system do what it does best - store and manage files.

Sometimes a handy feature with storing the image with the file system is when you need to update an image file manually. Instead of trying to get that image data back into a blob and updated, you just FTP the image up and you're done (assuming you work out the correct file name).

What is also handy is if you out grow the drive you're storing the images on, you can move them some where else and ideally update very little code (assuming you plan ahead and store the image directory location in a single place). The company I was at had to move the images (full size and thumb nails) several times because we kept filling the drive. But it wasn't a big deal. Storing those in the database would require the entire database to be moved to another server which is running the same DBMS. It can be done, but I think can produce more chances for something to go wrong and produce more down time.

loic_phpbuilder

thanks Pig and AstroTeg

regarding whay AstroTeg was saying :
"assuming you plan ahead and store the image directory location in a single place"

practically how would you do that ?

Initially I thought I would have :
table images
- id
- title
- description
- fs_filename
- fs_path
- thumb_filename
- thumb_path

So are you saying that "fs_path" and "thumb_path" should be somewhere else ? In another table ?

Also I have another problem at the moment... each image is offered for sale as prints (say 3 different sizes, 3 different prices).
Because of this 'matrix' like data, I'm not sure where and how to store it in the database.

Thanks again

Loic

AstroTeg

Ah, I see what you're doing...

fs_filename
fs_path
thumb_filename
thumb_path

The "filename" part is just fine for the full size and thumb size images. But the path you may find better stored in a script accessible file. But there's a few reasons to do this and not to do this. I'll explain and let you decide.

To save it in 1 spot (such as a file):
- Assumes all images are parked in one location (maybe have subdirectories for the different flavor of images such as thumb and full size)

When you need to move the images, you update the path in 1 spot (copy images to new location, update config file to point to new location, delete images from old location = no down time in theory)

To save the path in the database:
- The path will be radically different for each image (if it is, it would be difficult to move all the files to a new location and then try updating the database to reflect the changes)

The key to think about here is the fewer places you have code specify a specific directory location, the easier it will be to update the code to point to a new directory. Storing it in the database will always work, but can get messy later on.

If you're still stuck on storing the path data in the database, you might decide to make a config table to hold the directory information. That would mean at the path is written just once and you could easily query it. There's several ways to do this...

As for your database design, 3 images and 3 prices, you will probably want to break this out into a seperate table.

Just ideas here...

Image Metadata Table:
- imetID
- imetTitle
- imetDescription

(imet - there's a pattern to creating this - let me know if you want an explanation behind it)

Image Table
- imagID
- imetID (this links up with the metadata)
- imagFilename
- imagType (enum, or ID to a type table)
- imagPrice (assuming price is based on image and not image type - if on image type, then you might put price in the image type table)

Then you left join the image table with the image metadata table. This setup would allow you to have 3 images, 2 images or 100 images per title.

There's a couple ways to do this. I just listed the first one that came to my mind...

loic_phpbuilder

I see...
so there will be a config file or a table somewhere just to store a few settings
so what's best : file or table ?

regarding the different print sizes (and prices) for each image...
I see where you're going but I think in my situation it will create redundancy... for example imagFilename will be the same

see initially I thought we could have
images table :
- id
- title
- desc
- fs_filename
- thumb_filename
- print_a
- price_a
- print_b
- price_b
- print_c
- price_c

but it is likely most images in the database will follow the same pricing structure (same sizes available and same prices for a particular size... although I would like the option to have different pricing structures)

so what about moving the sizes and prices in another table instead ?
for example :
table limited_edition_1
- print_id (won't be really used I think)
- width
- height
- price

and who knows I might have more info such as 'freight'

then if some images need a different pricing structure we could just have a new table
table limited_edition_2
- print_id
- width
- height
- price

how would I link an image to a pricing structure ?
is it just a matter of giving the corresponding table name to the image ?
images table :
- id
- title
- desc
- fs_filename
- thumb_filename
- price_table

does I make sense...?

cheers

Loic

AstroTeg

You're at the database design point where knowledge of how your data works will effect the database design. You'll have to look at what kind of data you will be working with and determine for yourself how you want to store it. IDs will be your link between tables...

loic_phpbuilder

ok no worries

I'll do some more reading about database design and hopefully things will become clearer

thanks for all your help

best regards

Loic