Organizing Images with a Database

In: Articles

By Matt Thommes

Published on May 30, 2005

With the advent of Flickr, images have proven to be a powerful way to communicate on the Web—even more effective than words.

Flickr is a massive central repository of images from anyone of anything. By adopting popular interaction concepts such as tagging, feedback and profiles, Flickr has sparked the masses to share, invigorate, and inspire.

Flickr is one of the first major applications to harness the power of a relational database in direct combination with images.

The relational database provides immediate information about the images—dimensions, descriptions and alternative textual representations.

By using a database to organize and manage such information, you create “virtual classifications,” or “virtual folders,” which allow you to interact with your images like never before.

In particular, the types of images handled on Flickr (as well as images on e-commerce sites or photo blogs), are images that represent a concept or idea, and act as a visual aid to the words associated with them.

After all, we can only describe something to a point. Nothing speaks louder than an image.

Behind the Scenes

As impressive as images are to behold, nothing is more detailed and concise than the methods used to manage and maintain those images.

There are many different ways of handling a massive quantity of images. Flickr, e-commerce sites, and photo blogs all take different approaches. Some organize images in separate folders, while others name their images according to their meaning. Some don’t concern themselves with image organization at all. The smartest use a database.

The relational database allows you to store a large amount of metadata (information associated with individual images, such as width, height, alt text and title text) directly in database tables. You can then extract that information (through queries), to form image elements on your Web page.

Information is More Important than Location

Although the actual image files are stored in a single directory, not the database, the metadata relies heavily on the efficiency of database organization.

It’s key to understand that metadata for each image is more important than storage location (directory), because the image files hold no meaning.

The challenge is finding a simple and effective way to relate the images in FTP folders, with the associated image information in the database.

Database Table Structure

The structure for the database tables can vary, but should at least include the following fields:

• Date submitted
• Extension
• Width
• Height
• Alt text
• Title text
• Long description.

Each of these fields should be organized appropriately among two separate tables:

CREATE TABLE image_index (
`id` int(10) unsigned not null auto_increment,
PRIMARY KEY(id),
`date` date not null,
`time` time not null,
`extension` varchar(8) not null
);
CREATE TABLE image_details (
`id` int(10) unsigned not null auto_increment,
PRIMARY KEY(id),
`image_id` int(10) unsigned not null,
`width` smallint(4) not null,
`height` smallint(4) not null,
`alt_text` varchar(100) not null,
`title_text` varchar(100) not null,
`description` text null
);

The SQL code above will create the two necessary tables to hold the image information.

The image_details table includes the bulk of the image information. The width, height, alt text, title text and description are important informational attributes of each image. By relating the two tables with the id fields, we can combine both tables into one.

Naming the Image Files

Image names should not reflect meaning, appearance, or function. Rather, each name should be a unique set of numbers (or letters), which relate to the associated information in the database. For example, take a look at this sample URL for an image file in Flickr:

http://www.Flickr.com/photos/132375_0ca82ae31e.jpg

The name of the image is a unique mix of numbers and letters and is meaningful to the database. For our example, we should probably use the id field in the image_index table, as the name for each image. This will guarantee uniqueness, no matter how many images we have.

Virtual Classification

If you use an image management application like iPhoto, you’re probably aching for some classification.

With the right queries, you can generate any type of summary or classification you’d like. For example, let’s say we want to view or display all images that share a similar title text. This will help us gather images that are part of the same classification:

SELECT
image_index.id
image_index.extension,
image_details.image_id,
image_details.width,
image_details.height,
image_details.alt_text,
image_details.title_text
FROM
image_index,
image_details
WHERE
image_index.id = image_details.image_id
AND
image_details.title_text LIKE '<span>8×10 Glass Crescent</span>'

This query will pull all the images that have title similar to 8×10 Glass Crescent.

This is our virtual classification (virtual directories) in action.

Instead of using actual directories, you can organize your images by asking the database meaningful questions that return image information that matches certain criteria.

Other Unique Identifiers

Although the id field in the image_index table is unique, there are other approaches to creating a unique set of numbers or letters for your image names. For example, you could use the date and time fields:

20050315_090834.jpg

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

Earlier, I stated that the image names should not reflect their meaning, appearance or function. However, the date and time stamp does mean something, doesn’t it? It’s OK for your image names to hold some meaning, just as long as they are entirely unique.

Conclusion

The most important things to remember are that the location of images is irrelevant, and the names of the images must be unique and correspond to unique information in the database. The summary and classification, through queries, makes our “virtual directory” come to life.

This technique is just one of dozens of methods for organizing images with a database. The method you choose should depend on the number of images you need to work with and the goals of your project. I believe this method is the most versatile and well-organized.

• Add to Magnolia
• Add to My Yahoo!
• Add to Newsvine
• Digg this story
• Add to Del.icio.us

Related Topics: Databases, Photography