Database Design | Anthony Godley

Relational Database Design — What does it mean

Relational Database Design (RDD) can sound a little daunting, however when broken down it is actually quite a straight forward practice. There are nuances which elevate the complexity of RDD such as Database Schema, but the fundamental definition of RDD is relatively simple.

So, what is a Relational Database?

In its most basic form, a relational database works by storing information in tables, with each table consisting of a series of rows and columns.

The database is structured to recognise relationships between information stored in the tables.

The “relationship” between the tables is what makes the database relational. This structure allows for massive amounts of data to be stored and retrieved.

Here is a simple example. If a customer places an order on a website, there would be a Customer Table in the database as well as an Orders Table. The Orders Table will have line items which means there would also be a Line Items Table in the database. If an order has a related payment option, then there would also need to be a Payment Table.

And that is a Relational Database…

Quite Straight Forward.

Let’s now increase the complexity and look at Relational Database Schema Design and “SQL”

SQL stands for Structured Query Language and was developed by the International Organization for Standardization (ISO) in 1986. It was designed to manage data held in an RDBMS, or Relational Database Management System.

Since 1986 there have been many new version releases of SQL and a range of adaptations including SQLite, MySQL and PostgreSQL.

When creating a Relational Database it is important to have a considered database design approach.

What makes a good Relational Database Design?

When considering Relational Database Schema Design, the objectives of Data Accuracy and Integrity should be at the center of the approach. This is called Normalization.

What this means is data should be divided accurately among tables which will reduce the risk of data duplication and inconsistencies in the data. Flawed data accuracy will lead to data redundancy which go against the fundamental objective, “Data Accuracy”.

A considered database design approach should also accommodate data processing and reporting needs.

Designing Relational Database Schema

If you are designing Relational Database Schema, it is good practice to segment the design approach into 4 sections:

1. Define the purpose of the Database
2. Identify what the Primary Key is for each table
3. Determine what the relationships will be between tables
4. Check the Integrity of the Data and Refine

Define the purpose of the Database

To help determine what kind of information is going to be stored in the database it is important to identify objectives. You can sample queries and refine what results you want to achieve from each query.

You can now gather the data and information, divide into the individual tables and determine what the columns of each table will be.

Identify what the Primary Key is for each table

Every row within the table will have a Primary Key set as a field. The primary key uniquely identifies the row and can also be set against multiple fields. There is a slight difference in the terminology used when setting a Primary key. If you are setting a Primary Key against a single field it is referred to as a Simple Key. If set against multiple fields, it is known as a Primary Key.

Let’s look at an example:

Primary Key would be the product_id

In this example the Primary Key would be the product_id. The reason the Product ID was selected as the Primary Key is because the value can be set as an integer type and will be unique to every item in the database.

There are some things to consider:

1. You need to ensure that each row has a unique primary key. Having duplicate primary keys will lead to a breach of a fundamental objective, “Data Accuracy”
2. The primary key must have a value and not be set to null or 0
3. Using an integer type value keeps the data simple as fractional inputs increase the data complexity

Determine what the relationships will be between tables

As the database develops into multiple tables you need to determine the relationships between them. There are some commonly used relationship types used:

• One-to-One
• One-to-Many
• Many-to-Many

One-to-One: A One-to-One relationship is simple. One row from a table is linked to at most one row from another table. For example, a Product can map to a Product Item Description table which holds all the data about that product.

In the example below the two tables are mapped together using the common column and Primary Key “product_id”.

One-to-Many: In a One-to-Many relationship, one row from a table can map to multiple rows on another table. A good example of when a One-to-Many relationship might be used is in a book library. For this example we could use John Grisham. John Grisham is “one” author with “many” books.

One of the key benefits of a One-to-Many relationship is the storing of frequently used information in the Parent Table. The table with “one” (John Grisham) is always the Parent Table. You can then reference this table many times through the table with “many” (Books). We refer to this table as the Child Table.

In a One-to-Many relationship we set a Foreign Key in the Child Table. The Foreign Key is the Primary Key in the Parent Table.

Many-to-Many: A Many-to-Many relationship is the most complex database relationship structure. In a Many-to-Many relationship “one” or “many” rows in one table can link to 0, 1 or “many” in another table. For this type of relationship we need to implement a Mapping, Intermediary or Junction table.

A good example would be to consider a Newspaper and Subscribers. If there was a series of Newspapers and a series of Subscribers who were Subscribing to the range of Newspapers, a Junction Table would be used.

However, if you were to store additional information, such as the Delivery Rate, for example;

Newsletter 1: Customer subscribes to it once a week
Newsletter 2: Customer subscribes to it twice a week

This would no longer require a Junction Table and would become a Subscription Table.

In real life practice it is common that instead of using a Many-to-Many relationship, multiple One-to-Many relations would be adopted.

This approach is sometimes taken as a best practice Many-to-Many Junction Table would have only 2 key and limited content. It should not represent an entity such as an order or a transaction. As soon as you’re looking at an entity with its own data, then it’s not just a Junction Table any more.

A Junction Table uses both Primary Keys “product_id” and the “transaction_id” to map a Transaction, a Customer to a Product or a Product to a Customer.

Check the Integrity of the Data and Refine

It is time to check through the database for Data Accuracy and Integrity. What you need to ask yourself is:

1. Are there currently enough columns to represent the data?
2. Could a more comprehensive column be derived from a combination of existing columns?
3. Is there any data duplication?
4. Are there any empty fields?

Finally, it is important to ensure the Integrity of the information:

Entity Integrity Rule: This integrity will be flawed if the Primary Key contains null.

Referential Integrity Rule: Each foreign key value must be matched to a Primary Key value in the table referenced.

“And there you have it. An approach to good Relational Database Design.”

For more trending digital content and to learn how to outrank your competitors in 2020 join me on LinkedIn.