Organizing images with a database Mar04 '05

FTP image folders vs. database structure

Databases are great for storing and organizing TEXT. But images are certainly a part of web pages, as well. How do you organize your images in FTP, in relation to your database structure?

Since images can’t directly be stored in a database, they still must reside in an FTP folder somewhere.

Yet, at the same time, the image information could very well reside in the database (width, height, alt text, title text, etc).

The challenge is finding a simple and effective way to relate both the image FTP folder(s), and the image information in the database.

Today I am going to mention the method that I prefer, when dealing with a large quantity of images.

Photographs

First off, we’re only going to look at a certain type of image – photographs. By photographs, I am referring to images that represent something, and act as a visual aid to the plain text associated with it. The visual aid (more than just text) helps the viewer to gain a better understanding of the subject at hand.

Product image catalog

More specifically, we are going to look at a giant product image catalog, for an e–commerce site.

Let’s say you have a catalog of 400 product images, which will all be on the site somewhere.

You’d like to store your actual images in an FTP folder, and store their associated information (width, height, alt text, title text, etc.) in your database.

FTP first

The first thing you’ll want to do is organize your images in an FTP folder.

Try to avoid creating numerous folders, with different "classifications." Although this may appear to assist in organization, it will complicate things later.

To keep it simple, throw all of your images (yes, ALL) into ONE folder. Call that folder "images." From now on, all of your product images will reside in this folder.

To make better sense of this approach, consider the database model. We’re going to organize our images with the database, rather than in FTP. After all, the FTP folder (with the images inside) has only ONE purpose – to contain the images. FTP should NOT be responsible for providing more information ABOUT the images (or "classifications"). The database is responsible for that.

The database structure

After all of your product images are contained within that single folder, we can move onto the fun part – creating the tables for the image information. We only need two tables.

Please note that the structure and field names for these tables are my representation of the project. Feel free to change things around, and add more fields, if need be.

Create a table called image_index:

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
);

The id field above will also serve as the actual image name, in FTP. More on that shortly.

The date and time fields are simply the date and time that the image was entered into the database. And the extension field holds the image extension (typically jpg, gif, or png).

Next, create a table called image_details:

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
);

The image_id field is a foreign key that relates to the id field in the image_index table.

width, height, alt_text, and title_text are pretty self–explanatory.

Relation!

As you can see, the database tables contain all relevent image information. With the right queries, you could generate any type of summary or classification you’d like.

In the mean time, the actual image files still remain in that single FTP folder. Remember, they hold no meaning, other than just being there. The meaning comes from the database.

As I mentioned earlier:

The id field in the image_index table will also serve as the actual image name, in FTP.

How is this done?

Each image will be named with a number – starting with 1, and so on. That number corresponds to the id field in the image_index table:

1.jpg
2.jpg
3.jpg
...
400.jpg
etc

So... all you need to do to call a certain image is to use it’s number. And since that number has meaning, you can gather more information, based on it.

Categories: SQL , Tutorials , Web Development

Add Feedback (view all)