An illustration database is a good tool to help gather all source material in one place. Whether you are a Sunday school teacher, a helper in a class, a ministry leader or a senior pastor, having one location for your source material is very beneficial. There will be three articles in this series. This one covers creating the database. The second one will cover how to use the database. Finally, the third article will cover how to make the illustration database (and any important files) portable. Be forewarned, this article has a high geek factor, so if you are interested in this, find a geek in your life and ask him or her to do this for you.

Date Model

This illustration database is an easy one with three main tables. For simplicity’s sake, this is like a notebook full of paper to put information into. Each table will contain similar information. These tables are 1) Illustration, 2) Category and 3) Illustration Usage Log. The Illustration table will contain all the main illustration data. The Category table will contain categories to place the illustrations into. And finally, the Illustration Usage Log does just that: It logs the usage of your illustrations.

The first step in creating a database is obvious: you need to pick a database to use. In this case, an open source database from Open Office called Base is used. It can be downloaded at OpenOffice.org. Once you have installed the Open Office suite, you are ready to create the database (if you prefer to download the database rather than create it you can do so here.) The principles in this article apply to any database chosen (whether it is MySQL, FileMaker Pro, Microsoft Access, or Oracle).

Ensure the radio button is selected next to Create a new database and click Next.

Select the radio button for No, do not register the database.

Then ensure the checkbox is checked next to Open the database for editing and click Finish.

Now save the database, and the database program will be ready.

Creating Tables

The next step is to create the structure to place your information into. These structures are called tables.

First, click on the Tables icon on the left under Database. Then in Tasks, click on Create Table in Design View...

Now you are ready to create the table. Enter the Field Names and select the Field Types exactly like the picture above.

Click on each field and validate or enter in the corresponding information:

Next, right-click the gray box next to the IllID field and select Primary Key. Remember this because you will do this for the other tables.

Close the window (the "X" at the top right of the window) and click Yes to save the table. Name the table Illustration and click OK.

Now create a new table in design view for the Category table.

Set the CatID field as the Primary Key like you did in the Illustration table. Remember to close the window and set the table name as Category.

Now that the Illustration and Category tables are created. The last table to create is one  to log the use of the illustrations. This feature allows you to review where the illustration was used and helps prevent using the illustration in a location or series of messages or lessons. Create a new table in design view. Call this table IllustrationUsageLog when you save it.

Queries—Requesting and Sorting Data

Queries are used to request information from database tables and sort them as needed. Four queries are used for this version:

1. Query_Category - Requests and sorts Categories alphabetically.
2. Query_Illustration - Simple Illustration listing sorted alphabetically
3. Query_IllustrationCategory - Lists all illustrations grouped by category
4. Query_Location - A listing of all locations the illustrations are used.

Click on the Queries icon and click on Create Query in SQL View...

Copy and paste the following SQL (Structured Query Language) commands and click File | Save As naming each one respectively.

Query_Category

SELECT "Category"."CatID" AS "CatID", "Category"."Category" AS "Category" FROM "Category" "Category" ORDER BY "Category"."Category"

Query_Illustration

SELECT "ILLID" AS "ILLID", "IllustrationName" AS "IllustrationName", "DateAdded" AS "DateAdded", "Tags" AS "Tags" FROM "Illustration" ORDER BY "IllustrationName" ASC, "DateAdded" ASC

Query_IllustrationCategory

SELECT "Illustration"."IllustrationName", "Illustration"."Illustration", "Illustration"."IllLink", "Illustration"."IllSource", "Illustration"."IllMemo", "Illustration"."DateAdded", "Illustration"."Tags", "Category"."Category" FROM "Illustration", "Category" WHERE "Illustration"."CatID" = "Category"."CatID" ORDER BY "Illustration"."IllustrationName" ASC, "Illustration"."DateAdded" ASC

Query_Location

SELECT "Location"."LocationID" AS "LocationID", "Location"."LocationName" AS "LocationName", "Location"."City" AS "City", "Location"."State" AS "State", "Location"."Country" AS "Country" FROM "Location" "Location" ORDER BY "Location"."LocationName", "Location"."State"

Forms—The User Interface

This is where you create the user interface and work with the data you put into the database. You never want to manipulate the data from the tables because this can cause problems if you are not careful. A properly designed form will be your gateway to the information inside the tables of the database. The main form that will be created is called the Illustration form. The second one is the Category form and the last one is the Illustration Usage form.

The easiest and quickest way to create forms for the database is to use the Form Wizard. Click on the Forms icon and click on Use Wizard to Create Form...  Create a form for each of the following and use the appropriate query (not table) as your data source:

1. Category Management Form—Allows you to add and edit categories.

2. Illustration Central Form—The main form used for illustration data entry and editing.

3. Illustration Usage Form—Allows for the selection of an illustration and a data entry area to enter where the illustration was used and any relevant notes.

4. Location Form—Allows for the addition and editing of locations.

The form creation is up to the user’s needs. It can be as complicated or easy as you desire based on your specific needs. When using the forms, the next record button saves the data you type in. That button is at the bottom of the form and faces to the right.

If you would prefer to download the database instead of creating it, you may do so here. Remember to install Open Office before trying to use the database.

Please check back for the second article in this series.