Entity Relationship (ER) Diagramming

[jkrutz]

ER Diagramming Tutorial

An ER Diagram is a tool used to tell you, the developer, how your database is organized or help you conceptualize how to organize it. It details the relationships and attributes of key components called Entities.

Entities

Think of an entity as encompassing term for a set of objects (like a Table in Excel). For example, my name is Josh, I live on Street X, my phone number is 719-555-1234, and my social security number is 222-22-2222. While I have many attributes (columns, if you are visualizing a table) that make me unique, I am but a simple Person. If we were to represent people in Entity form it would look something like:

Attributes

As stated earlier, think of attributes as columns in a table. In the example below, attributes of a Person are listed as columns: SSN, Name, and Phone Number. Attributes should only really describe the entity they are attached to. For example, it doesn't really make sense that a Person should have an attribute called PetName, instead that should just be part of a separate Table/Entity.

SSN	Name	Phone Number
222-22-2222	Josh	(719) 555-1234
555-55-5555	Andrew	(512) 555-1234

Primary Key

An attribute that is unique to a record, does not change, and is never null is a great primary key. Primary keys are used to identify exactly one record in a Table. In the Person example, using a name will sometimes identify one individual, but it is possible for two people to have the same name. For this reason we will use SSNs (Technically it is possible for more than one person to have your SSN, so just bear with me for this example). To identify the Primary Key we mark the attribute in the table with "PK."

Foreign Key

A foreign key is super similar to the primary key, except it originates from another table. Going off of our Person example, lets add a new table: Pet. Each Pet has a microchip ID, its own name, and an owner.

The owner is in fact a Person, so we will actually pull this data from the Person entity. To do so, we label the owner attribute as a Foreign Key or "FK." Unlike a primary key, foreign keys do not have to be unique and can be repeated. You can also have multiple foreign keys in one entity.

Relationships Between Entities

Entities are great, but the magic of a database is that it pools together the needed tables to deliver information to whoever requests. To illustrate how the database combines tables we need to tell it what to do! Each Entity will have a line that connects it to another Entity, and these lines specify the minimum and maximum number of entities involved in each side of a relationship. We will use the following connectors:

In the example below, we illustrate the relationship between Pet and Person. A Person can have zero or many Pets, and a Pet belongs to one and only one Person (think only one Person can purchase a Pet). It is also common practice to have the connector directly link from PK to any exported FK.