Converting an ERD to a Physical Database

By: Dan D'Urso
Published:

The purpose of this article is to provide the student with an approach to converting an Entity Relationship Diagram (ERD) into a physical database. It is is a top down approach modeled after Teorey and Kroenke. It assumes the database implementor is going to be using the Microsoft Access GUI tool to generate the DDL "behind the scenes" and is familiar with this tool. If coded by hand many steps would be combined. In fact, the same is true with a GUI, but thinking of it as four steps can still be helpful.

This article does not treat some advanced topics such as relationships involving three or more entities. Rather, we will focus on "binary" relationships involving two entities which are those most commonly encountered in commercial practice.

An alternative bottom up approach modeled after Hernandez will be presented in a later article.

In a nutshell there are four steps:

Covert the entities into tables. There are four "sub-steps" here depending on the relationship(s) the entities participate in:

one-to-one

one-to-many

many-to-many

supertype-subtype

Apply business rules to tables created by setting field (or column) constraints appropriately

Create references between tables (in Access called permanent links) and referential integrity, applying appropriate business rules.

Create appropriate indexes. For the typical OLTP database we are considering here this would mean ensuring that primary and foreign keys are indexed.

I am beginning to think there is another step that can be placed right here: create supporting views (in Access - saved queries). But we'll leave this off for now.

Step 1: Convert Entities

One-to-one relationships

Two entities participating in a one-to-one relationships are each converted to a table. Typically the table name is plural where it makes sense. For example: Employees, Parts, Suppliers, etc. Some people further like to prefix the table name with tbl or an equivalent convention. For example: tblEmployees, tblSuppliers.

The primary key of one of the tables is placed in the other table as a foreign key. Which table gets the foreign key depends on your application. If for example we had a field engineering application with car and engineer entities where at most one car was assigned to an engineer we might place the foreign key in the engineer table. We would do this because we would typically be working with the engineer table but might occasionally want to see information about his or her assigned car. Either will work.

One more thing: optionality. If the assignment is optional, then set the foreign key to allow nulls.

One-to-many relationships

This will probably be your most common case. Fortunately, it is also the simplest. Each of the two entities is converted to a table. The primary key of the table on the one side is placed in the table on the many side as a foreign key. Again, if the relationship is optional, set the foreign key to allow nulls. (In Access this would mean set the required property to no. We will discuss field properties in greater depth later.)

Many-to-many relationships

Your design may or may not have any many-to-many relationships. You may have "flattened" the design already by converting the many-to-many to one-to manies. We'll assume your diagram still has a many-to-many relationship.

First convert the two entities to tables. Then create a third table, often called an intersection or junction table (but there are many different terms used). If there is no obvious name, give it a name representative of the two other tables. Example: students are in a many-to-many relationship with courses. We could call the intersection table student_courses. In this case, though, we can probably use a "real" name such as enrollments.

more