Notes from 52nd International STC Conference
Seattle, Washington, May 8-12, 2005

Building a Database of Graphic Files Using Microsoft Access

Richard B. Lowe
Thomson Scientific

Lowe has worked as a technical communicator at Thomson Scientific for 20 years. He holds MS degrees in technical and science communication and in library and information science, both from Drexel University.

Session Description: This workshop is based on the premise that managing a growing collection of graphic files can be frustrating or even disastrous. It taught participants how to build a fully normalized relational database for storing and managing graphic files using Microsoft Access.

• Requirement: Impose order on randomly filed graphics that are stored all over the place on the hard drive.
• Bad Solution: Massive table in Word with each graphic individually listed, including all products in which it appears.
• Better Solution: Create a relational database.
• "Flat" File Database. Use a wizard with fields for the parameters you want to record. Create one when each graphic is created, putting it into the relational database. Problem: limited characters in fields, cannot accommodate different versions of files.
• Relational database in Access is much more flexible.
• Steps to build a relationship database:
   
• Understand the product domain and understand the relationships. Define the information you want to retrieve.
• Create an entity relationship diagram (for complex databases). Excel provides a good alternative, creating a high-level schema for the conceptual design of the database.
• Example of using the Excel schema as a means of creating a relational database:
   
• Pick out significant nouns and devote on column in the Excel chart to each one (e.g., GRAPHIC, PRODUCT, DELIVERABLE).
• Identify properties for each significant noun and list them in the Excel column underneath the noun.
• Be sure that every item in the database has a unique identity (the primary key; underscore primary keys)
• Determine relations: 1 to 1, 1 to many, many to many (cardinality) e.g., GRAPHIC-PRODUCT, GRAPHIC-DELIVERABLE, DELIVERABLE-PRODUCT.
• Normalize. If one attribute changes, you change it in one place and the change then ramifies throughout the database.
• = primary key in a different relation ("foreign key")
• Handle cross-attributions properly. When an attribute describes another attribute, it must become an entity (weak entity) e.g., PRODUCTNAME
• Key point: Always do your conceptual design first (Excel schema), before going into Access (or any other relational database program, such as Filemaker Pro). The rest is mechanics.
• Working in Access:
   
• Start with design view; do not use wizard for that.
• Create tables in Access (each column in Excel becomes a table in Access
• Identify the primary key (on toolbar).
• Add all tables into the Relationship window; do not start doing queries or reports until all relationships are established.
• Create Access forms for each primary entity; sub-forms for other various relations.
• Next step is to make inquiries and pull up reports. The presenter ran out of time at this point and did not demonstrate making inquiries and pulling up reports. He said that was pretty much self-explanatory from the pull-down menus.