anatomy-relational-databases

Anatomy of a Mobile Application: Relational Databases

October 5, 2016 Blog

(Mobile applications have become so smooth that we can forget about the many unseen pieces making it work. It’s easy to forget that the user interface is only a piece of a much larger architecture. A successful mobile app has a layer of backend components to support it. When planning to create a new mobile application, or update an existing one, the pieces you don’t see will need as much attention as the UI. This series will explore those backend pieces needed to create a great mobile application.)

What is a database?

The data displayed inside of a mobile application needs to be read from somewhere before it can be shown. This is typically done using databases; even if the database sits on a server behind a REST API. However, the term database covers a huge world of possibilities. A database can be anything from a simple text file to a mind-bogglingly complex data warehouse. Different types of databases store information in different ways from key-value storage to documents to relational tables.

Mobile applications can use one or more type of database depending on design and platform. In fact, it is quite common to use a combination of databases in a single application. The server can store data in a relational model while data can also be stored locally on a device in a document store.

Databases are a large topic so this series will break them up a bit. This article will focus on the relational database model. The next piece in the series will discuss non-relational databases.

What is a relational database?

Relational databases leverage the relationships between data points to organize them. Databases are organized into tables. The tables store data in columns and rows. Although relational database systems will differ regarding features and capabilities, all systems use the table as a base component.

Mobile application developers can use a relational database as one potential data strategy. Relational databases can be utilized locally on the client device or exist on a server. In some cases, both environments may employ a relational database. They are one of several data storage mechanisms in the mobile application developer’s toolbox.

Database tables can be illustrated using a table with each column representing a field, and each row representing an individual record. Going forward, let’s pretend the tables below are contained in the same database and hold the listed data records:

AUTHORS TABLE
UniqueKey FirstName LastName Nickname
1 John Smith Johnny
2 Sarah Bland Sara
3 Daniel Rich Danny

BOOKS TABLE
UniqueKey Title AuthorUniqueKey
1 My Favorite Day 3
2 Here is To Tomorrow 3
3 Just Because 2
4 I Said So 2
5 The Day He Said Boo 1

One table in a relational database can have a relationship with one or more other tables. That relationship can be used in many different ways, such as using a field in one table to refer to records in another table. Our pretend database’s tables use this design to connect Authors to Books via the Author’s UniqueKey.

The below diagram is a very simplified Entity Relationship Diagram (ERD). ERDs are used to plan and describe database structures. It shows the UniqueKey values in the Authors table are stored in a field on the Books records. Books in the Books Table identify their author using the Author’s UniqueKey value.

All relational database models will have these characteristics:

Tabular
A tabular data structure stores information as a set of tables with columns and rows. Columns define the fields stored in the table and their format. Rows represent individual records.

Schema Driven
A schema in a relational database identifies the fields, and their formats, stored in the database. A relational database relies heavily on a solid schema design. Changing the database schema once it is in use can be a difficult and cumbersome process. Upfront schema design is an important part of any relational database implementation.

Foreign Keys
Data in one table can create a relationship with another table by storing keys. Data is not duplicated from one table to the other, instead one table can refer to another. The relationship between the tables is defined by the Books table storing of the Author’s UniqueKey in the AuthorsUniqueKey field. The Author’s information doesn’t need to be duplicated with each Book row.

Data Integrity Constraints
Constraints force the operations performed on data in a relational database to meet certain criteria. Data can not be added, removed, or updated if it violates any of the database’s defined constraints. For example, our Authors and Books tables could have the following data integrity constraints:

  1. There can be multiple Books per Author Id.
  2. Every Book in the Books table must have an Author in the Authors table.

Data integrity constraints force the database to follow certain rules. Let’s say we wanted to remove John Smith from the Authors table. If John Smith has no Books in the Books table, then the database would allow John Smith’s row to be deleted.

If John Smith has one or more books in the Books table, then the database would not allow the deletion. It would not allow the deletion because of the constraint that every Book must have an Author in the Authors table. Removing John Smith from the database would create orphaned Books with no Author in the Authors table. If every book by John Smith were deleted first, then the database would allow the deletion of the John Smith Author record.

Relational database are excellent for maintaining data with business rules and in a trustworthy state. The data in a relational database is guaranteed to follow its schema.

When To Use a Relational Database

It is best to use a relational database when the application’s data schema is easily planned and unchanging. All applications, including mobile applications, that need trustworthy storage to pull together disparate data will use a relational database.

Relational databases are best when:

  • The database schema can be planned ahead of time
  • Data has strong relationships that would result in duplicate data otherwise
  • The database schema is unlikely to change

Some example uses for a relational database:

  • Inventory system
  • User database
  • Billing System

Conclusion

Relational databases are key to most application stacks, and the mobile application is no different. It is not uncommon for a mobile application to use a relational database on the server through a REST API. It is equally as likely to use a relational database for its own local storage. The ability to force data to meet certain forms and criteria means the data can be trusted. Trusted data can then be used to pull related data points into new and useful formations.

David Posin

Web Developer and Blogger

Leave a Reply

Your email address will not be published. Required fields are marked *