Digital Web Magazine

The web professional's online magazine of choice.

Organizing Images with a Database : Comments

By Matt Thommes

May 30, 2005



May 30, 2005 11:04 PM

Great article, organization is something I really struggle to deal with everyday.


May 31, 2005 8:25 AM

Very interesting reading… but what if you don’t know much about databases… how can I apply the information contained in the article practically??!!???


May 31, 2005 8:54 AM

I got tired of looking for an existing system to suit my needs, so I built my own image database.

I don’t store height or width information, as I read the information directly from the images own built-in meta data. (Both GIF and JPG images store quite a bit of info already, so no need to duplicate.)

I wanted a system that would allow for lots of metadata: city, country, category, people, year…

You can see it in action here:

Now, if I could only find the time to keep it updated…


May 31, 2005 9:03 AM

“Since no more than one image can be inserted at a particular second, your image names will all contain a unique set of numbers.”

Why not? I don’t think this is a good assumption at all.

Lisa Giovanni

May 31, 2005 9:24 AM

This is so unbelievably great article. Thanks.


May 31, 2005 9:43 AM

In fact, “no more than one image can be inserted at a particular second” is a catastrophe waiting to happen. No offense Matt, but your article leaves a lot to be desired as regards databases. First of all, use an identity or AUTOINCREMENT field to guarantee uniqueness, depending on the RDBMS. Second, your sample SQL should be checked by an expert before posting on an article such as this one. For example, you should not use comma-joins. You need to learn ANSI join syntax. In fact, as your article uses the database tables, you need to normalize your two tables into one; and finally, normalize your date and time fields into one as well.

To other people reading this article, if you are not proficient enough with databases to understand my comments, I can only say “hire an expert who understands why it is better to avoid the bad practices in this article.”


May 31, 2005 3:01 PM

Is there any real reason not to go ahead and store your images in a database with the BLOB datatype?


May 31, 2005 3:40 PM

“Images are an important part of Web pages, too, but can


May 31, 2005 4:38 PM

Michael and Jack – thanks for your feedback.

I want to make clear that this is intended to be a simple, practical approach to organizing images – and most importantly – generating “virtual folders,” instead of manually creating different folders, for different groups of images.

Sure, it’s certainly possible to store images directly in a database with the BLOB datatype – but that’s not practical – and it’s not the idea I am trying to convey.

I am also referring to e-commerce sites, which could sometimes have hundreds or thousands of stock images to put on a web site.

Again, the most important point is that by writing the appropriate queries, you can grab out images, based on their meta-data. This is the main idea.

And, for example – if you have a group of images of a certain product, you can write another query that pulls out images of similar products, to display beside the current product. You get the idea.

The more meta-data that you have about each image, the more distinct and descriptive your “virtual folders” become – which makes your web site “smarter.”

Ian Williamson

May 31, 2005 7:46 PM

True, it may not be practical. However, stating that it is “not possible” would not be appropriate because that is obviously not an accurate statement. Perhaps explaining the practicality of storing the image in the database would be better.

Adam Bramwell

May 31, 2005 9:47 PM

I think I just got ‘should’d into guilt


June 1, 2005 4:50 AM

When the same question about storing images in a database came up on my host

Robert Wetzlmayr

June 2, 2005 12:37 PM

Besides the naive assumption that no more than one image could be added to a database at any given second (which will definitely happen in a multi user web application, according to Murphy) I would strongly advise to omit the claim of Flickr being one of the first major applications to harness the power of a relational database in direct combination with images..
Anybody seen Corbis lately? Veer? Documentum DAM (virtual folders galore)? On a lower level, Textpattern image categories?.

What did you expect where all those sites and applications stored their image meta-data? How those could return query results that quick? By scanning a folder hierarchy? You wouldn’t dare.

Baron already dropped some facts on SQL and database basics. Agree.

Dave P

June 2, 2005 1:23 PM

To pick up on Robert’s point: Google Images anyone? I’m sure there’s a Database invloved there somewhere.

At any rate, I’ve noticed that this isn’t the first time that more technically based articles on this site are a little off.

If the editors are having difficultly with vetting articles for best practice I’d be more than happy to offer my services where I can.

It doesn’t help anyone to have code examples littered with corrections/complaints all through the comments.


June 2, 2005 5:10 PM

Hi all.

I wanted to clear up some of the code examples used in this article, based on the suggestions that many of you have made.

Baron suggested AUTO_INCREMENT columns to guarantee uniqueness – and maybe it was overlooked, but the AUTO_INCREMENT columns are there in the code. They are used for the ID field in both tables. This is what guarantees uniqueness for the actual image file name.

In regards to using two tables, instead of one – I agree, the fields could easily be combined into a single table. Perhaps this is bad practice on my part – and thanks all for pointing that out.

Baron also suggested not using COMMA JOINS. COMMA JOINS is the simplest approach to gathering information from multiple tables, and I wanted to keep things as simple and straight-forward as possible. Whereas it is certainly better practice to use ANSI JOIN methods, the COMMA JOIN approach still works fine.

Also, Baron suggested combining the DATE and TIME fields into one. This is also bad practice on my part – and they should be ONE field, not two.

Other than that, I don’t see what else is “off” with the SQL code.

Robert and Dave P – in regards to “Flickr being one of the first major applications to use a database with images” – I wanted to tie in Flickr somehow, since readers are very familiar with the Flickr web site. Obviously, there have probably been MANY other applications that have used a database to organize images – I just mention Flickr because people could relate to it. Sorry for the confusion.

Ian, I’m not sure how that statement got in there – honestly… I knew saying “not possible” would raise some eyebrows – and I had thought I took it out.

Also, I didn’t want to get into too much database syntax, code, and explanations – there are plenty of other fine Digital Web articles explaining that – my main point was coming from the “image organization” side of things.

I apologize for the slight misjudgements in the SQL code, but they are all easily rectifiable.


June 2, 2005 11:14 PM

But could someone point us to ‘the expert’s approach’ when it comes to the creation of tables for a mysql image database?

I am sure lots of readers here desperately need a good start to manage their own snapshot gallery.

Andrew Henze Qwent

June 3, 2005 3:12 AM

Dear Mr. Digital-Web
You can do better than this!

Krista Stevens

June 3, 2005 3:15 PM

I think some of the more advanced programmers in the audience may have misinterpreted the intent of Matt’s article. The intent was to show one method on how you can use SQL to store images in a database by providing some thoughts and some code ideas for a beginner, who maybe never knew they could do this and might want to try it as a first foray into database programming. Clearly, the intent was not to make expert SQL programmers out of us all. I welcome discussion on Digital Web, because the beauty of this community is that we teach each other. Got a best practice? Please do offer it. Do it in such a way that we all learn, not to chide a volunteer author. Good discussion is how we all can contribute to building the Web. Thanks for reading.

Krista Stevens
Editor in Chief
Digital Web Magazine

Frank Manno

June 7, 2005 6:56 AM

Despite the overall “feel” and “atmosphere” created by the majority of comments, I’d like to give kudos to the author. From a beginner’s stand point, it’s definitely a step in the right direction.

I remember when I first started off with DB programming… I wanted something that was both simple and easy to understand. The intent of this article was centered around image management (at least that’s my take on it), and I think he achieved it fairly well.

Sure, a few mistakes… but who hasn’t made mistakes? Hell, we still do.


June 7, 2005 6:07 PM

I found this article interesting because it is something I spent quite a lot of time thinking about when I was building my photo gallery system in PHP/MySQL… I store all my images in a single directory and let the database metadata structure them as needed. However, I always wondered a bit about storing ALL images in a single directory – are there any issues in terms of file system / OS performance when you have a directory with potentially tens or hundreds of thousands of files in it?


June 10, 2005 10:00 AM


Based on my understanding of file structures and retrievals, at numbers like that, you may start to see tiny performance decreases – depending on the number of requests. (I don’t remember what the recommended max number of files per folder


July 10, 2005 11:24 PM

Ed, what you have overlooked is that along with the factual errors and bad advice rampant in this article is that it is very little help to a total beginner. Here is my 3 sentence summary of the article:

Smart people create a table to store image metadata. The table should have image metadata properties as columns. You can query the table to get images that match your criteria.

Where is the glue that sticks this all together? Is the beginner supposed to guess that this solution requires all their images to be renamed to match the database identifier after a row of metadata has been inserted? Maybe. Does the beginner know they will have to append the identifier in the DB to the physical path of the image in the directory structure? Possibly. There are a lots of omissions such as these.

Perhaps you should get hold of someone with a little more expertise as it might help improve the quality of your submissions, of which this is not exactly a shining example.


July 11, 2005 8:13 PM

It could be intelligent to offer a follow-up to this piece, in which a few of the considerations and techniques would be explained further, including the issues identified with binary objects in databases, example code, etc. Importing binary data isn’t a very intimidating prospect in itself, if explained clearly.

It could be interesting for those not familiar with these concepts if the follow-up went into things like incorporating controlled vocabularies, too..

I’d invite this contributor’s input/involvement, too. I’ve got a vague feeling the issue was just an error in wording, not incompetence.

some guy

July 14, 2005 2:46 PM

one thing that nobody mentioned: why put those <span> tags into your database? what if you want to change them to <p> tags later, or you re-code your pages in such a way that the <span> is not necessary? it’s never a good idea to put HTML markup into your database. databases are for storing DATA, not presentation-layer markup.


October 30, 2005 6:45 PM

I agree with all the disparaging remarks on the artcle. I feel some of the remarks should be addressed to the editors for not fully accepting responsibility for allowing the article to be published in an unfinshed form. There must be some pressure to fill bandwidth.

I suggest the book by Welling and Thomson “PHP and MySql Web Development”. There are several books out by Hernandez, and als by Bowman on SQl which would prove useful.

Nick Finck

October 31, 2005 9:18 PM

Funny, I didn’t see the article as being in any way in “an unfinshed form”. Perhaps you missed our Introduction to Databases article? Perhaps you feel that every article should take someone who knows nothing about a subject and make them PHDs? Maybe your efforts would be better spent doing something about it.

Sorry, comments are closed.

Media Temple

via Ad Packs