How to plan your Access or FileMaker Pro database – Part 3

Before creating a database you should have some blueprints that will guide its creation. In part one of this series we talked about having sketches of input forms and reports. Part two was about creating a list of fields in a “Field List Worksheet” document. Now we are going to talk about creating an ER Diagram. The ER does not stand for Emergency Room. However creating a database without an ER Diagram can cause so much frustration that you will think you need to go to the hospital.

What is an ER Diagram?

“ER” stands for Entity Relationship. What is an “Entity?” In plain English an “Entity” is anything that you might track in the database. Each type of entity should be represented by a table in the database. Therefore an ER diagram is a diagram showing the different types of entities (tables) in the database and how they are related to each other. Some ER diagrams, like the one below, also show the fields of each table. Notice that it also has lines drawn between each primary key and the corresponding foreign key, thus showing the relationships between the tables.

How do you create an ER diagram?

An ER diagram can be created with just paper and pencil or with software. The following steps explain how to create an ER Diagram with Microsoft Excel 2010.

1. Open Excel.
2. Enter the name of a table in a cell.

   

3. Designate that this cell represents a table name by changing the background color to gray or some other color of your choosing.

   

4. Enter the name of the primary key field in the next cell down.

   

5. Designate that this field is the primary key by making the it bold.

   

6. List the table’s other fields in the cells below.

   

7. Repeat the process for the other tables.

   

8. Add space between each table group. This will make drawing lines easier.  Specifically insert narrow columns and blank rows between each set of tables.

   

9. Turn off the gridlines. On the View tab, in the Show group, uncheck the Gridlines option.

   

   

10. Apply a border around each table including all its fields. First select the appropriate cells. Then on the Home tab, in the Font group, click the border drop down, and select All Borders.

    

11. Draw a line from each primary key to the corresponding foreign key. On the Insert tab, in the Illustrations group, click the Shapes drop down and select the arrow shape. Then draw the arrow from the primary key to the corresponding foreign key.