Impact of intended usage patterns on database design

The simplest database consists of just one table. However, as has already been mentioned in the section on files, most (if not all) databases have multiple tables, up to tens of thousands of tables, and sometimes even more. The most important step in creating a new database is to decide which tables you need in order to store your data efficiently for its intended use and what relationships those tables have with one another. Data modeling is the process of deciding on that table structure and thereby deciding on the schema.

Note

Data modeling is the process of deciding which data structures to use for storing data. With regard to relational databases, this translates into deciding on the table structure (schema) to use.

There are different concepts for modeling data structures. We can, for example, normalize databases, invented by E.F. Codd. E.F. Codd translated mathematical set theory into formal steps to reach an optimal schema. Normalizing a database involves following these formal steps. It is often said that a database is in the third normal form, meaning the steps have been applied to the data until the third step. You will learn how to normalize a database in Chapter 3, Normalizing Data.

In Chapter 7, Dimensional Modeling, and Chapter 9, Data Vault Modeling, you will learn about alternative data modeling techniques. These alternative techniques lead to different database schemas. Query performance and query complexity depend heavily on the schema you choose. In Azure, this may translate directly into how expensive your data solution is going to be. If you need more processing power to execute a query, you might have to provision a higher performance tier of the service you use, which will cost more money. With a different intended use, meaning you use different queries, a different schema may be beneficial.

A database supporting a web shop should be able to quickly retrieve all information about a specific product. In addition, it should be easy and quick to create a new shopping basket as well as new invoices. This type of database is best designed by using the technique of normalization. A data warehouse should be able to quickly retrieve vast amounts of data and aggregate that data. A report based on a data warehouse should, for instance, show all the sales data of an entire product group over the last 12 months. Where a webshop often retrieves single rows from the database, a report often retrieves millions of rows. A dimensionally modeled database will be better in retrieving millions of rows than a normalized database.

In Chapter 5, Designing a NoSQL Database, we will look at NoSQL databases. NoSQL databases have their own rules depending on the type of database you use.

Whatever database you use, the similarities will be bigger than the differences from a modeling perspective. This is especially true for all types of relational databases. A database is always a collection of data, a set of data, that can be described by set theory. The next section will describe how the theory behind databases has its foundations in mathematical set theory.