SQL Database

cgstock.com home prints site info Minneapolis licensing links index search feedback

Linux commands perl regexp graphics Essays Vilana Financial Author Bio Hotwire.com

dreamhost

Other sections within this website

Minneapolis
China
Philippines
Prints
Categories
Locations
Events
Jim Pickerell
Bob Firth

I use an SQL (Structured Query Language) database server to support perl programs that create this website. I use MySQL specifically, and have another page with useful MySQL commands. This page describes the photos database I've designed to support this website.

I use the perl Database Independent Interface (DBI) package, which makes it possible to access MySQL from perl programs.

The database I created holds all the information on the photos in this site, as well as all the other photos I've downloaded from my camera. It has all the text pages, links, and visitor comments, too. I update this database with a graphical perl program (I call tkphotos, because it uses perl-tk). This program makes changes to the database, and another program re-writes the website's pages and and uploads them to the webhost.

The photos are organized into 5 sections (Minneapolis, Categories, Locations, Personal, and Drawings), each with several sub-categories. There are thumbnail and full-sized images for each photograph. Each has it's own, static webpage with detailed information (date, format, title, description, etc.). This has any related hyperlinks or visitor comments.

photos database

I named the database "photos". It has about 20 tables, each with a unique, auto_incrementing key. Below is some of the output from the MySQL command "show tables", which lists all the tables in a database.

+------------------+
| Tables_in_photos |
+------------------+
| categories       |
| comments         |
| format           |
| links            |
| pages            |
| photos           |
| sections         |
| shared_html      |
| templates        |
+------------------+

photos table

The most important table is the one called "photos". Pictures are added to the website by inserting a new imagename, title, date, and description into the photos table. An id number gets assigned to the photo. When this number is added to one or more category, the image becomes included in the website under that category.

Below is a description of the photos table (this is the output of the MySQL command "describe photos;")

mysql> describe photos;
+-------------+-----------------------+------+-----+
| Field       | Type                  | Null | Key |
+-------------+-----------------------+------+-----+
| id          | smallint(5) unsigned  |      | PRI |
| imagename   | varchar(50)           |      | UNI |
| w           | smallint(5) unsigned  | YES  |     |
| h           | smallint(5) unsigned  | YES  |     |
| tw          | smallint(3) unsigned  | YES  |     |
| th          | smallint(3) unsigned  | YES  |     |
| title       | tinytext              |      |     |
| taken       | date                  | YES  |     |
| keywords    | text                  | YES  |     |
| description | text                  | YES  |     |
| deleted     | enum('y','n')         |      |     |
| modified    | date                  | YES  |     |
| copyright   | tinyint(2) unsigned   | YES  |     |
+-------------+-----------------------+------+-----+
14 rows in set (0.01 sec)

Below is a description of each field:

This is the unique key for the table, and is auto-incrementing. It is the photo's id number, and is used for the webpage URL. So image number 1234 is located here:
http://www.phototour.minneapolis.mn.us/1234

imagename

This is the filename for the photo. This allows me to locate the original jpeg file on my hard drive, when necessary.

w,h,tw,th

This is the image width, height, and thumbnail width and height.

title

This is the title used for the image

taken

This is the "date taken" for the picture (this field is not called "date" because that is a reserved word..."date" is a column type).

keywords

This is a list of keywords which are entered into each picture page's meta keywords field. These are terms also used by the website's search engine.

description

This is the image caption, which states where the picture was taken and what's depicted.

deleted

This field can only be 'y' or 'n'. Pictures can be in the database, but (for editorial reasons) not included in the website. They don't have to be deleted from the database, though, since they might be added back to the site later. If this field is set to 'y', the photo is not included in the website.

modified

This is the date I last edited the information for a picture.

The way photos are organized into sections and categories is handled by two other tables, named "sections" and "categories". The sections table has a field for the categories that it's made up of (this is a list of comma-separated category id numbers). The categories table then has a field for comma-separated photo id numbers.

mysql> describe sections;
+-------------+---------------------+------+-----+
| Field       | Type                | Null | Key |
+-------------+---------------------+------+-----+
| id          | tinyint(3) unsigned |      | PRI |
| name        | varchar(50)         |      | UNI |
| title       | varchar(150)        | YES  |     |
| description | tinytext            | YES  |     |
| keywords    | tinytext            | YES  |     |
| categories  | text                | YES  |     |
+-------------+---------------------+------+-----+
7 rows in set (0.00 sec)

mysql> describe categories;
+-------------+---------------------+------+-----+
| Field       | Type                | Null | Key |
+-------------+---------------------+------+-----+
| id          | tinyint(3) unsigned |      | PRI |
| name        | varchar(50)         |      |     |
| title       | varchar(150)        | YES  |     |
| description | tinytext            | YES  |     |
| keywords    | text                | YES  |     |
| photos      | text                | YES  |     |
+-------------+---------------------+------+-----+

The links table contains a field for photos. This is a comma-delimited list of id numbers from the photos table.


 mysql> describe links;
 +-------------+----------------------+
 | Field       | Type                 |
 +-------------+----------------------+
 | lid         | smallint(5) unsigned |
 | title       | tinytext             |
 | url         | tinytext             |
 | description | tinytext             |
 | keywords    | tinytext             |
 | photos      | tinytext             |
 +-------------+----------------------+
 6 rows in set (0.00 sec)

This is the table for comments:


 mysql> describe comments;
 +-----------+----------------------+
 | Field     | Type                 |
 +-----------+----------------------+
 | id        | smallint(5) unsigned |
 | photo     | smallint(5) unsigned |
 | name      | varchar(35)          |
 | posted    | date                 |
 | residence | varchar(50)          |
 | email     | varchar(50)          |
 | comment   | text                 |
 +-----------+----------------------+
 7 rows in set (0.00 sec)

"photo" is the id number from the photos table for the picture the comment goes with.

templates and shared_html

The "templates" table has templates for the different kinds of pages that make up this site. Thumnail, picture pages, and text pages each have their own HTML template.

The shared_html table contains snippets of HTML that are shared among various kinds of pages. Headers and footers, for example.

The perl program that generates this website inserts data from the photos table into the appropriate spot in the pic_page template, once for each photo in the database, skipping those who's deleted field is set to 'y'. The resulting page is saved with the image's id number for it's filename.

View database design

I have put an empty version of this database online, which shows the exact MySQL commands that creates the database tables. The link below will display this as a textfile.
photos.sql.txt