Design the Logical Model of Your Relational Database

Relational databases have been available for a number of years, and are the chosen standard design for the daily needs of most businesses. Similar to how we describe our relationship to those around us, a database is made up of tables that are related to one another in some way.

Think of a relational database table as a spreadsheet tab with specific information listed in the row at the top, such as a book title, and corresponding information listed in each column, such as the author, publication date, and so forth.

This traditional method allows data to be stored in only one location, reduces redundancy, and minimizes the chance that it won't be updated when editing.

What is NoSQL in Relation to Relational Databases?

I’m sure you’ve heard of Not Only SQL (NoSQL) and many alternatives such as Couchbase, MongoDB, and Cassandra, to name a few. This is a much newer flavor of specialized database compared to a traditional relational model database.

NoSQL databases can be grouped into four general types, though you may see them classified a bit differently based on the source you locate. Many also overlap into more than one category. The categories are:

• Column

• Graph

• Key-value

• Document

Let's take a look at what you might find out there.

Column

First, the column version uses a timestamp to differentiate between the valid and stale or old values, and are typically used in a distributed environment with versions that sync up at given intervals. A popular example of this type of NoSQL database is Cassandra, which you can find as a free open source variety. The latest version can be found on their website.

Graph 

Next, are the graph varieties (based on graph theory) that transfer data into nodes and relationships into edges. An example of this type of database is Neo4j, which is also open source and was recently listed as one of the most popular graph databases based on DB-Engines Ranking, which is updated monthly.

Key-Value

The key-value variety of NoSQL stores data as a dictionary or hashtable, which is a collection of records. Simply put, the key is the dictionary word, and the definition is the value. The value can be made of any number of individual values separated by a comma.  If you want to try this variety, Oracle NoSQL is a good example and has a community edition that can be downloaded and used for free.

Document

Finally, there is document-oriented NoSQL that stores all the details of an object in a single instance in the database, such as the details related to a vehicle purchase. Here again, there is a community edition you can download and use with an open source license. We’ll take a look at a more in-depth example of the document variety with Couchbase. The graphic below shows the different database models and how they are structured. 

SQL vs. NoSQL

We’ve looked at an overview of the varieties of NoSQL database types available. Let's look a little more at the differences between them and relational databases.

Relational databases use SQL, or Structured Query Language. So naturally, they are highly structured. SQL allows you to query the database. The database is also scalable thanks to the structure, which is essentially a network of predefined tables.

NoSQL databases are dynamic in structure. Building a NoSQL database requires less preparation than building a relational database since there isn't a predefined structure. NoSQL databases are useful for collecting unstructured data.

Since NoSQL databases are more specialized, they tend to fill a niche or specific need (i.e., the Couchbase document store data model) like car sales:

A car salesman might have an app to view recent purchases that are loaded or updated when new deals are added. The advantage would be less data usage. The document storage model saves on their data plan by not querying a relational database, but rather by having the documents uploaded and stored on their device. That is, the signed contracts or deals would be accessible to those who have the app on their phone. The dealership would still have a relational database that stores the individual components for reporting and regular use on a desktop or more traditional portable devices. The deal is a historical record, and the data would never change. Managers and salespersons could use this to follow-up on recent purchases. It could also help them understand what vehicles are trending, among other possibilities. The data that needs updating, such as the customer phone number, employment information, home address, etc., would still be maintained by the relational database.

As technology changes, NoSQL databases play an important role but are not a replacement for the relational database model.

Relational Databases

Relational databases store details about an entity, such as a vehicle manufacturer, and those related like vehicle make, which string together meaningful records. This concept keeps duplicate records to a minimum and maximizes the likelihood that changes can be successful by only storing a single piece of data in one location.

Relational databases are often associated with websites and other transaction-related applications.

Let’s look at an example:

A bookstore may need to store data related to books, authors, and purchases. This data should be stored in separate tables, as illustrated below. The Books table would contain unique information like the title, price, and publication date, while the Authors table would contain the authors’ first and last names. The graphic below shows how Books and Authors are related. The BookAuthor table comes from the normalization process, which we will discuss in Part 3! 

What is an Entity?

An entity is anything that exists, such as a person, place, or object, which can be somehow described.

For example, a person is an entity that can be described by their name, birthdate, address, favorite movie, and so on.

Let's now look at how relational databases are put together from entities. You've seen that relational databases are essentially made up of tables that store data. One entity translates to one table. So the logical model of the books and author data above, organized into three entities, will  translate into three tables in a database. 

Describing Entities With Attributes

Now that you have entities defined, let's look a the qualities that describe each of them. In a database, these qualities are called attributes.

For example, if you had a vehicle as an entity, one of its attributes might be its color. Or, if your favorite restaurant is an entity, its postal code would be one of its attributes.

While entities become the tables of the database, the attributes become the fields within the tables.

For example, a Vehicle Table might have a color column, and for each record representing a specific vehicle, you'd find a field where the color of that vehicle could be stored as data. Likewise, a Restaurant Table might have a postal code column.

Attributes are very specific and atomic; in other words, they’re broken down into their smallest components.

For example, an address is not atomic because it has several components. So a complete address wouldn’t be considered an attribute; rather, you’d break it down into several pieces. In the case of your favorite restaurant, the street and building number would be one of its attributes, the city would be another, the state or province or county another, and finally, the postal code would be yet another. When defining attributes, make sure they are as small as functionally possible.

Some attributes have many potential variations. For example, there are several varieties of color: red, blue, orange, etc. It's useful to use a Lookup table to store the possibilities for one attribute that has specific and fixed values. In this case, that would mean creating a Colors table where specific colors can be stored.

Another example: If you have books with specific genres such as drama or romance, then you would create an entity to hold only those genres in a table of the same name. While the Books table would contain book-related details, the Genre table would contain the genres with the GenreID linking the two tables, usually in the form of a numeric value, as shown below:

How Are Entities Related to One Another?

Once the entities and attributes are established, you need to determine how they relate to one another. Each will be related to at least one other, so you maintain at least one relationship between each table.

Good question, but no, that is very rarely the case, and we will look at some examples. Diagrams known as Entity Relationship Diagrams, or ERDs, are created to show how the tables are related. As more and more entity tables are added, an ERD becomes essential for keeping their relationships straight, as you can see in the image below. For a relational database to function correctly, entities must be related to one or more separate tables. 

The relationships are based on the primary key (PK) and foreign key (FK). As you look over the diagram above, you’ll notice all tables are related to at least one other, with the Books table having the most (four). Since each entity contains only the related attributes, there must be a link between tables for meaningful data and records to be retrieved. Later on in the course, we will discuss the concepts of primary and foreign keys, which are critical for entity relationships to exist and to maintain a healthy and up-to-date database.

Let’s Recap!

• NoSQL databases are increasingly popular and tend to fill a niche.

• Entities are anything which you can describe and are made into tables in the database.

• Attributes describe entities and are broken down to the smallest or atomic level.

• Entity relationship diagrams are the blueprints for your database. They define the entities, attributes, and relationships between them.

Now that we've got a basic understanding of relational databases and how they compare with NoSQL, let's learn the value of database management systems.