Converting an ERD to a Physical Database

View from Laguna Beach in Orange County, Southern California

Articles Index

Course Listing

Free Catalog

Request Information

What's New 

Page 1 | Page 2

-Microsoft Access Example-

Close Window

Step 3: Create Relationships

In Access these are called Permanent Links. Once created they are available to the design tools, wizards, etc. The other kind of a link is a Temporary Link created in the Query Designer that exists only for the duration of a query.

In the Relationship Designer add the tables you have created to the design surface.

To create a relationship between two tables drag the primary key from the parent table to the child table's corresponding foreign key. Enforce referential integrity. This maintains the integrity of the relationship between the two tables. Child table foreign keys will always reference a parent table record with a matching primary key. What to do when this is not the case will depend on you business rules.

Child records can be added only if there exists a parent table record with a primary key that matches the child's foreign key. If no corresponding parent table record exists an error message results.

If an attempt is made to delete a parent table record with child table records that reference it, or to modify its primary key, an error message results. That is unless cascade options discussed below have been selected.

Cascade Options

These correspond to ON <action> CASCADE. in SQL.

Cascade Delete

Causes the related child records to be deleted along with the parent table record. You probably want to be cautious about when to use this one. Set it according to your business rule(s).

Corresponds to ON DELETE CASCADE

Cascade Update

Update the values of the foreign keys in the child records to reflect the new parent record primary key value.

Corresponds to ON UPDATE CASCADE.

Join Type

You have one more decision. What type of join do you want the relationship to implement. There are three choices: INNER, LEFT OUTER, AND RIGHT OUTER.

Inner Join

This is the default and is the typical join used in most cases. It will retrieve all records from the two tables with matching key values.

Left Outer Join

This join will retrieve all records from the two tables with matching key values plus any records from the first table that do not match. The missing values from the second table will be filled with NULLS.

Most database books do not emphasize how common this type of join really is, especially for reporting purposes. In our field engineering example, say that you wanted to see a list of all your engineers and details about the car they were assigned if one was assigned. An inner join would not return all engineers. Only those with cars assigned. A left join would return all engineers, filling in car information with NULLS if no car was assigned.

You probably are not going to set up many of these as permanent links but be aware when you go to the query designer what type of join has been created for you.

Right Outer Join

This join will retrieve all records from the two tables with matching key values plus any records from the second table that do not match. The missing values from the first table will be filled with NULLS.

I have not seen this join type used very much.

Full Outer Join

There is also a full outer join which includes unmatched records form both tables. Access does not support this join type, although many other relational databases do.

Step 4: Create Indexes

Back to the table properties. We left off discussing indexes until last because this area is related to performance. You may need to experiment a bit. A relational database, and this includes Access, will work without explicitly creating indexes. That is the good news. The bad news is that performance may be poor. Before discussing which columns to place indexes on we need to review the two types.


This type of index ensures that all entries are unique. Primary keys would have this type of index. It can also be used to ensure uniqueness of, for example, social security numbers, in an employee table with badge number as the primary key.

Not Unique (allow duplicates)

These indexes allow duplicate values. They can be used to speed searches and reporting and for foreign keys.

Guidelines - When and Where to Index

Although a seemingly simple enough subject, indexing is actually a very large subject. In this article we are barely scratching the surface. As a rough rule of thumb:

  • You want indexes on all primary and foreign keys

  • In addition to the above, use indexes only on highly selective columns (more about this later) or when required to enforce a business rule.

  • There should be no more than about 4 to 5 indexes for the typical OLTP type database table. A read intensive reporting database can have up to about 12 or so.

Access will have set up unique indexes for the primary keys automatically. For the foreign keys set the indexed field property to yes(allow duplicates) if Access has not done so automatically.

You may also want an index or two on columns that will be searched frequently. However, do this only if the search column is highly selective. I.e., if you expect the typical retrieval operation to return 20% or less of the records in the table then an index will help. Otherwise, due to the way relational database indexes are structured the database will wind up doing less work just scanning through the table a record at a time. The exact percentage will vary depending on the particulars of your database. Remember, I said you might have to experiment a bit.

If your business rules require certain fields, other than primary keys to be unique, then set the corresponding indexed field property to yes(no duplicates).

Return to Page 1

Dan D'Urso
Laguna Niguel
Orange County, CA 92677
Updated November 2017
articles at dhdurso dot org

Please send comments and suggestions to WebMaster at

Copyright 2017 D.H.D'Urso & Associates
Laguna Niguel., Orange County, California

 IndexClose Window | Back | Top