We've been using tables with columns and rows to represent our databases. That's fine when we only have three dogs. But when a database has thousands or millions of records, we need a better (smaller) way to show the relationships between the entities in each table. Such a tool exists, and it's called an ERD (Entity Relationship Diagram). Below is an ERD of our database as it is structured so far (1NF):
This ERD shows two tables: DOG and SKILL. Tables should be named in the singular (e.g., not "DOGS" and not "SKILLS," but your employer may have a different standard). The top box is used to show the name of each table, and then the fields in the table are listed below that. You will also notice a line drawn between the two tables, which shows a relationship. On the left side of this line, there are two vertical slashes which means "must exist." On the right side of this line is a circle and a three-pronged triton, which means "zero or more." When you put it all together, it reads "One dog can have zero or more skills" with the understanding that the dog must exist before it can have any skills. This type of set up is known as a "one-to-many relationship."
One way to view an ERD is to see the entities as the "nouns" of your data: person, places, and things. Attributes (columns or fields) are the "adjectives" for those nouns, because they describe (a person's name, a person's age, a person's address, etc.). The relationships between entities are the "verbs."
Entity relationship diagrams come in various flavors, and both vendors and scholars
have invented their own notation systems. The notation used in the ERD above is a hybrid
cross between two very common notational systems (one for the logical, and one for the
physical). A logical ERD is a high level view that focuses on just the table relationships,
and doesn't include details about fields. On the other hand, the physical ERD includes
information on the fields in a table, and shows how they work as keys to connect one
table to the next. Here is how our example database would look in each diagram type:
| LOGICAL ERD | PHYSICAL ERD | |
|---|---|---|
![]() |
![]() |
When interpreting a logical ERD, a double vertical bars on the connecting line mean that entity "must exist" before the relationship can be established. In all the examples below, a dog must exist before it can have a skill, owner, funeral, etc. The symbols on the end of the lines are generally a combination of one or two symbols. The first symbol represents the minimum, and the second (or only symbol in some cases) represents the maximum. The symbols are a circle (means "zero"), single vertical slash (means "one"), and a three-pronged triton (means "many"). Here are some examples and how they are read:
![]() | One DOG can have ZERO or MANY SKILLs |
![]() | One DOG can have ONE or MANY OWNERs |
![]() |
One DOG can have TWO or MANY ANCESTORs (because we didn't specify a minimum of 1 or 0 the minimum is an implied 2) |
![]() | One DOG can have either ZERO or ONE FUNERAL |
Try your hand at it now, with these examples:
These statements could easily have come from a design session, where a database designer interviewed a user. By asking specific questions he can identify the entities to be tracked, and how those entities relate to each other. Those complex relationships can be visualized in an ERD produced from such an interview.
Logical ERD's are great for overviews and planning, but eventually you'll have to deal with the details. The physical ERD gives that kind of detail. Specifically, it shows the keys (index keys) that are used to link two tables in a relationship.
In a physical ERD, the shape of a box is significant. Boxes with square corners are considered independent entities. Boxes with curved corners are dependent. You will also note that a box is divided into two sections. Fields listed above the horizontal divider are primary keys. Fields below the line are secondary keys. In a dependent entity, you'll also see the suffix <FK> after a field name. This stands for "Foreign Key." A foreign key is simply a link to a Primary Key in another table (i.e., the link from a child record to a parent record). The primary key in a table must be unique, but that key may be duplicated many times in the dependent (child) table as a foreign key. For example, Nip is dog number 12. There will be only one dog #12 in the DOG table, but there can be many records in the SKILL table with dog #12 entries. That's because a single dog can have many skills.
Here are the meanings for the different connecting lines in a physical ERD:
|
| One to Zero or More | |
|
| One to One or More | |
|
| One to Exactly n (The letter "N" would be replaced with the appropriate digit. For example, there are exactly three members in a triad.) | |
|
| One to Zero or One | |
|
| Zero or One to One or More. The diamond represents the limit on the origination side of the relationship. Also note the dashed line, which specifically means that a relationship is non-identifying. This would be the case of a lookup or validation table. |
This is the notation we'll use for this tutorial. It borrows the best from several different notation systems, including the physical and logical notations already shown, so that we can more accurately diagram a wider selection of relationships. In the section "An Example ERD" (at the top of this page), I show the hybrid notation in use: The boldly outlined box at the top of each column contains the table name in all capital letters. The fields in each table are listed below the table name. Relationship lines are drawn between the linking fields, and not just between table names. Therefore, the ERD at the top of the page shows that DOG.DOG must pre-exist, and may have zero or more occurrences in SKILL.DOG.
Read the lines below from left to right. The different symbols are: vertical slash (one), circle (zero), three-pronged triton (many), double-vertical slash (must pre-exist), and solid circles (mutually exclusive). The dashed lines have a house-shaped arrow head (initialized from, or copied from) while the dotted lines have a triangular arrow head (conditional). We use two different arrow head shapes just to make it easier to distinguish dotted and dashed lines.
![]() | One to One |
![]() | One to One or More |
![]() | One to Zero or One |
![]() | One to Zero or More |
![]() | One to Many (2 minimum, because neither 1 or 0 is "many") |
![]() | One (must pre-exist) to Zero or More |
![]() | source is copied to destination (non-identifying relationship) |
![]() | Conditional relationship exists. E.g., an INVENTORY table has a COMPANY field which is conditionally interpreted by COMPANY.TYPE (where type might be "Distributor" or "Manufacturer"). We would draw a relationship from COMPANY.COMPANY to INVENTORY.COMPANY, and a conditional line from COMPANY.TYPE to INVENTORY.COMPANY. |
![]() | One (must pre-exist) to Zero or More. However the vertical slash before the line divides means you must choose one path: there is a "fork in the road." The solid circles after the fork mean "mutually exclusive." That is, take the top fork or take the bottom fork, but it can't be both simultaneously. |