Database Basics, Part 3: Data Models and Database Design

Surely you might have wondered by now “How do I graphically represent my database entities and their relationships?”

As with the rest of this series, this post doesn’t claim to be complete or to cover all possible points in detail - it is meant to give you a good overview of terms and concepts I feel would prove valuable for beginners, and which I would have wanted to have a few weeks ago when I started on this journey. You will find links to in-depth references at the end of this post.

Data Models

I’m going to show you two ways of representing database relationships: conceptual models and database design models. But first let’s recap entities real quick and then I’ll introduce you to the degrees of relationships as first step:

Refresher on Entities and Entity instances

• An entity or entity class is a description of the structure and format of all of that entity’s occurrences (remember the blueprint or recipe).
• An entity’s instance is a specific occurrence of an entity (remember again one house or one cake).

Entities

• Entities have attributes which together describe that entity (e.g. for a Project we might have ProjectName, StartDate, etc.)
• Each attribute has a data type (string, integer, etc.) and other properties.

Entity instances

• Instances have identifiers (keys)
• A key identifies a specific instance in the entity (e.g. EmployeeId identifies a specific Employee, and DepartmentId identifies exactly one Department).

EmployeeId	Name	DepartmentId
101	Eric	1
102	Daniela	2
103	Thomas	2

  ⬑ unique key                                  ⬑ non-unique key

Degrees of relationships

Entities can be connected to each other (e.g. “An employee has a fixed workplace.”, or “Many parts can be acquired from many suppliers.”).

The degree of relationship defines the number of entities that are participating in the relationship:

• Degree 1: Unary relationship (one participant)
• Degree 2: Binary relationship (two participants)
• Degree 3: Ternary relationship (three participants)

Conceptual models

Conceptual models are used to illustrate which data is going to be stored in the database, the structure of the data, and the relationships between the data. In this article I’ll introduce you to the Entity Relationship Model (E-R Model).

Example: the entity Employee shown with all its attributes.

Conceptual unary relationship

When only a single entity class is involved in the relationship, it is related to itself in an unary relationship:

In this example, an employee has another employee as supervisor. Another example would be “a person is married to another person.”

Conceptual binary relationship

When two different entity classes are related to each other, it is called a binary relationship:

Here, an employee has a fixed working space.

Conceptual ternary relationship

And when three entity classes participate in the relationship, it is called -you guessed it- a ternary relationship:

In the above example, a doctor prescribes a patient a treatment (like a drug or surgery).

Connectivity

Connectivity describes the classification between entities. I will use binary relationships as examples here (involving two different entity classes) since they are the most common cases.

Conceptual One-to-One relationship (1:1)

A single entity instance in entity class A is related to a single entity instance in entity class B.

Using the example I mentioned earlier when talking about degrees:

• An employee may have no more than one workplace.
• A workplace may only be used by one employee.

Conceptual One-to-Many relationship (1:N)

A single entity instance in one entity class is related to many entity instances in another entity class. Example:

• A department can have many employees.
• An employee works in one department.

Conceptual Many-to-Many binary relationship (N:M)

Many entity instances in one entity class are related to many entity instances in another entity class. Example:

• A supplier may supply several items.
• A particular item may be supplied by several suppliers.

Though this one looks simple in concept, it can not be realized directly in a database without the need for a third entity serving essentially as a look-up-table. This is called an intersection table.

A data excerpt of the example above might look like this:

Cardinality

Relationships are named and classified by their cardinalities - the “number or count of items in a set”: 1:1, 1:N, N:M, 1:7, etc.

The minimum cardinality defines the minimum number of entity instances that must participate in a relationship instance. It is typically either zero (meaning the instance is optional) or one (the instance is mandatory).

The maximum cardinality on the other hand defines the maximum number. Is it one, then there can only be exactly one instance.

You’ve already seen the crow’s foot symbol for “many” before. Here’s the complete list of cardinality symbols:

One

Many

Exactly one

Zero or one

One or many

Zero or many

Alright, let’s examine this example:

• The minimum cardinality for Customer and Order is one each.
• The maximum cardinality for Customer is one, and many for Order.

This means that

• each customer can place one ore more orders, and
• each order is associated with exactly one customer.

For further reading about relationships, I suggest the second half of Database Design: Chapter 9 Integrity Rules and Constraints

Now that we’ve got a broad overview of data models, let’s have a quick look at Database Design:

Database Designs

How that we have the conceptual overview of the entities, it’s time to design them. In general, we follow these steps to get all the needed parts:

• Create a table for each entity.
• Specify a primary key.
• Specify the needed column properties. Those are:
  • Data type
  • null status (are null values allowed?)
  • Default values (if any)
  • Data constraints (if any), e.g. maximum length of a string
• Analyse the relations to other entities using the normalisation rules (see part 2 in this series).
• Then, if and as normalisation issues arise, you may need to modify the initial design.

Representing relationships

The maximum cardinality determines how a relationship is represented:

1:1 relationship

• If both sides of the relationship are optional, either table can receive the foreign key. It doesn’t matter which one.
• If only one side is optional, the optional side receives the foreign key.

In the example above, in a hospital, “not every bed is occupied by a patient (some are vacant) but every patient present has a bed.” Since Bed is optional, Patient gets the foreign key.

1:N relationship

• As we’ve seen in the 1:1 relationship, the primary key from one table is placed into another table as a foreign key.
• The foreign key always goes into the many side of the relationship.
• The 1 side of this relationship is called parent, while the N side is called child.

“A team has zero to many players. While a player has only a single team they’re playing in.”

N:M relationship

I’ve described earlier that Many-to-Many relationships can’t be implemented directly but need an intersection table to serve as a look-up table (also called an associative entity).

• An intersection table usually has a composite key, which is comprised of the keys from both tables which it is connected to.

A data model representation of an example N:M relationship:

A database design representation of the same example needs to include the intersection table:

And a data excerpt of this example would look like this:

Association relationship

A association relationship or association table is a type of intersection table (a table with attributes comprising of the primary keys) which has additional attributes. The above Student_Class intersection table could have the attribute Grade for each combination of studentId and classNumber.

When an associative entity uses a composite primary key composed of the primary keys of its parent tables (as Student_Class does), each possible matched pair of value can appear only once in the association entity. (e.g. each student could only have a single grade in each class).

Using a surrogate key however allows each possible matched pair of values to appear multiple times in an associative entity:

On to the final sprint.

We’ve seen how to represent entities and their relationships to each other in two different ways, learned about connectivity and cardinality. In the next part, we’re going to talk about transactions and a few further concepts of which I think are good to have heard about, before we end our discourse…

Up next: Database Basics, Part 4: Transactions, Locking and Indexes

Previous topics:

Database Basics, Part 1: What are Relational Databases?

Database Basics, Part 2: Data Normalisation

---

General credits and further readings:

Adrienne Watt’s “Database Design - 2nd Edition” book

Dr. Daniel Soper’s “Database Lessons” videos