Raw data can be omnipresent, indefinite, and ubiquitous. Yes – I wake up every day to the smell of freshly ground cardamom as if it were my alarm (my alarm, on the other hand, fails the one job it was designated to do). Anyway, I get ready and drive to work using Google Maps for directions (I like to keep an eye out for traffic, so I choose my routes wisely), log the day’s ongoing work for my reference later, check the lunch menu at work in the app, connect with colleagues in Chat (and in person sometimes), work out with my favorite YouTube videos, order food/groceries online, post and connect via social media, and play sleep stories from my meditative collection as I get through and sway my day effortlessly into the non-REM (Rapid Eye Movement, the fourth stage of the sleep cycle) part of my sleep cycle while my subconscious brain starts to pick up where I left off. This cycle repeats as I wake up the next day to the smell of tea and freshly ground cardamom, wondering how nice it would be with some buttery soft sliced bread!

Hmm... if only it weren’t for my gluten allergy.

Anyway, the one thing that is half as old as sliced bread but twice as good as that is databases! Imagine the volume and variety of data I share, consume, and involuntarily indulge in throughout my life while undertaking the routine that just I shared (and perhaps I need to get a life)! What goes into accommodating those activities and what resources do I need to accomplish them?

That’s right! The set of application programs that store, access, manage, and update this data while dealing with structure, recovery, security, privacy, concurrency, and more, and attribute comprehensively to getting the day in the life of a modern-day human done right, is the database. It is also called the database management system or DBMS.

A teeny-tiny bit about the evolution of databases

Long before the term data was even coined, humans used the Ishango bone (what is assumed to be a notched baboon bone) as a tally stick speculated to have some mathematical engravings or even something of astrological relevance. Dating to 20,000 years before the present, it is regarded as the oldest mathematical “database” (logging numerical information for future use) tool for humankind, with the possible exception of the approximately 40,000-year-old Lebombo bone from southern Africa. Then, we have Acharya Pingala from the third to second century BC who first described the binary number system that lives on today – forming the foundations of any computing there is, including database systems. Slowly and steadily, we progressed into advanced computing, databases, and technology in general with calculators, computers, automation, wartime wonders, relational database management, the internet, Google Search (yes, it has come to be identified as an important event in the evolution of technology), artificial intelligence (AI), machine learning (ML), and big data.

Isn’t it fascinating how everything important dates back to monkeys or monkey bones, just like computers and homo sapiens themselves?

DBMS

Exactly 52 years ago, E.F. Codd, the father of DBMSs, propounded and formalized the 12 commandments, of which there are 13 (starting from 0. I know, right?), that make up a DBMS. You can read about it here: https://en.wikipedia.org/wiki/Codd%27s_12_rules. We have evolved since the 1960s, when we used one database to store and secure information, to modern times, where we use one database per stage in the data life cycle – that is, one database per data stage, type, and structure in most cases. We will dive deeper into each of these categories throughout this book with examples and exercises, so don’t panic if this is a jargon overdose at this point.

In this chapter, we are going to discuss the business attributes, technical aspects, design questions, and considerations to keep in mind while designing a database model.

Database design or modeling refers to the activity of designing a database and modeling the data and structure that stores, transforms, manages, and extracts data. Why is it important to design a database so elaborately, you ask? Here are some reasons:

• Data is any organization’s most valuable asset and to leverage it to derive the most benefit for the company, we need to ensure it is thoroughly planned and thought through
• As the current generation databases are easy to set up and use, the most common side effect is that business users tend to dive into creating databases with flawed and much-simplified structures without understanding the components of design
• With poorly designed databases, the systems end up having inaccurate results and difficult-to-trace errors, which leads to inconsistent decision-making in the business

If that does not serve your purpose, then consider this: the database is the most critical lifeline of your entire technical architecture as it runs across and connects all the components of your design. It starts with user interfaces, operational systems, messaging services, monitoring systems, analytics solutions, AI/ML applications, and even executive dashboards in the visualization layer that you use for business decision-making. If that lifeline is not well-thought-out, then you are starting your business and technology journey on a path full of surprises, twists, and turns. I understand those are good on paper but very inconvenient when it comes to real-world applications. Imagine having to redesign the foundational component of your understanding 6 months into rolling out your product!

Data modeling

Data modeling is the process of organizing the elements of your data and establishing their relationship with each other and external systems. Within the chosen database environment, a data model represents the structure, attributes (characteristics), relationships, transformations, business rules, and exceptions. There are a lot of data modeling frameworks and tools available in the market, so pick the one that works with your database model, structure of data, business rules, operations, and components of the data.

Database modeling

Database modeling refers to the process of determining the choice and logical structure of your database and designing the way you store, organize, and transform data. I would like to think of a database model as something that needs to be well-vetted out, so that should be the case with the data model. But in a sense, a data model can go through many rounds of trial and error and can evolve as you build compared to a database model, which needs to be designed for scale and also to acclimatize the data model it contains.

Several questions and considerations go into the design and model of a database. That is exactly what we will focus on as a framework in the rest of this chapter.

Why is it important to take a lot of possible scenarios and probabilities into account while making design decisions? Let me tell you a story that I read in a book called How Not to Be Wrong, by Jordan Ellenberg, an interesting read that talks about real-life applications of mathematics.

Like many other things, our story dates back to World War II, when things happened frequently. In this case, the US fighter planes entered combat with loaded machine guns. They didn’t want their fighter planes to get shot down by the enemy fighters. So, a statistical research group (SRG) of extraordinary statisticians was organized to aid the war effort, where equations were developed (and not explosives) to find a solution. Here’s the question that was posed to them: “You don’t want the fighter planes to get shot down, so you need to armor them, but armor makes the plane heavier, and heavier planes are less maneuverable and consume more fuel. Not too much, not too little, but we need to armor the planes somewhere at the optimum level.”

The military gave some useful data to the SRG: when American planes came back from a mission, they were covered in bullet holes. However, the damage wasn’t uniformly distributed across the aircraft. There were more bullet holes in the fuselage than in the engines. The bullet-holes-per-square-foot distribution was as follows:

• 1.11 in the engine
• 1.73 in the fuselage
• 1.55 in the fuel system
• 1.8 on the rest of the plane

The officers saw an opportunity to quickly conclude that the armor concentration needed to be in the area with the most bullet holes per square foot. One specific answer from the smartest statistician in that room contradicted this popular opinion, yet it was very insightful. Abraham Wald stated that the armor doesn’t go where the bullet holes are the most concentrated, but where they aren’t. Wald’s insight was simply to ask: where are the missing holes? The missing holes were the ones that would have been all over the engine casing if the bullet hole distribution had been spread equally all over the plane. Wald was sure that the missing holes were on the planes that had been shot down. The officers simply observed the planes that had returned from the mission but the ones that got hit in the engine had not returned at all!

I am sure by now you have already connected the dots as to why it is important to assess all the “missing holes” before you settle on the choice of database and the database model – it is because you don’t want to armor the wrong part of your business and end up spending more effort in the long run. If you’re wondering whether there is a quick and dirty solution, the short answer is no. However, a good design comes with a set of considerations around business attributes and technical aspects while designing the database model.

Business requirements are the starting point for your application and also for choosing your database system. There are four stages in the life cycle of data in its business application that help determine the choice of database system:

• Data ingestion
• Storage
• Process
• Visualize

The following diagram represents the attributes in the four stages of data and the categories of questions in each stage in the life cycle of your data:

Figure 1.1 – Representation of the four stages of data and the categories of questions in each stage

Let’s look at some of these attributes in detail. Some of them are in the business attributes category, while others are technical.

Ingestion

This is the first stage in the data life cycle and it is all about acquiring (bringing in) data from different sources in one place into your system. In this stage, the questions that arise are bucketed into three categories:

• What type of data are you bringing in?
• What is the purpose of this data?
• What is the structure of your data?

Let’s take a look at each in detail.

Types of data

There are broadly three types of data we will be dealing with that highly influence the choice of database and storage.

Application data

This is the kind of data that is generated or downloaded as part of the application’s content and can contain transactional data that is generated by users and applications – for example, online retail applications, log data from applications, event data, and clickstream data. Let’s take a look at a specific example – consider a banking application in which user A transfers money from their account to user B’s account. In this case, the user data, such as the account ID, name, bank details, the recipient’s name, and transaction date, constitute the application data.

Live stream and real-time stream data

This data comes from real-time sources such as streaming data, which comes in continuously from data sources such as sensor data. These can also be event data responses and can be very frequent compared to batch data processing. It refers to data that is immediately available and not delayed by a system or process. The term real-time stream refers to streams of real-time data that are gathered and stored or processed as they come in. This includes monitoring data such as CPU utilization, memory consumption, Internet of Things (IoT) devices data such as humidity and pressure, and automated real-time environmental temperature monitoring data.

Batch data

This is data that comes in as bulk at scheduled intervals and could be event-triggered. For example, batch data is transactional data that comes in from applications after a transaction and is stored for use in later stages of the data life cycle. This can include data extracted from one application for use in another at a later point, data migration use cases, and file uploads for processing later. Such applications may not be designed for real-time operations on the data.

The purpose of data

The specific use case and the nature of implementing applications using the data being ingested is a critical factor in determining the choice and design of the database. There may be cases where the type and ingestion mode of data fall into a different choice of database design, whereas its functional use case would imply a different purpose. For example, you could have data streamed in from live events or housekeeping data coming in real-time from transactions, but the specific use case you are designing for might only involve visualization, analytical, or ML functionalities. So, make sure you understand what purpose you are solving with the data that is being ingested in a specific mode and type.

The structure of data

The structure of the data is a crucial factor in deciding the choice and design of a database. There are three widely recognized categories:

• Structured
• Semi-structured
• Unstructured

Let’s briefly explore these three categories.

Structured data

This type of data is typically composed of rows and columns; rows are entities or records and columns are attributes. Structured data is organized in such a way that you can be sure that the data structure will be consistent for the most part throughout the life cycle of that data, except for the possible addition or removal of some attributes altogether. This kind of data is mostly transactional or analytical.

Semi-structured data

Semi-structured data does not follow a fixed tabular format – that is, a column-row structure. Instead, it stores schema attributes along with data. The attributes for semi-structured data could vary for each record. The major differentiating factor for each kind of semi-structured data is the way they are accessed.

Unstructured data

Unstructured data includes images, audio files, and so on. Unstructured data does not have a definite schema or data model. The amount of unstructured data is much larger than that of structured data. So, the methods by which we store such data are more important than ever. Here are some examples of unstructured data:

• Text
• Audio
• Video
• Images
• Other binary large objects (BLOBs)

Now that we have had a sneak peek into the structure of data, be sure to include functional and design questions based on these categories while designing your database and application model.

Whether you are engineering or architecting, ask the right data questions! There is always this question about the responsibilities concerning data. When designing data architecture, you must manage the business and technology requirements around the architecture, be involved in designing data extraction, transformation, and loading, and provide direction to the team for methods of organizing, formatting, and presenting data. Once you’ve done this, you’ll be an architect.

As an engineer, you create applications and develop solutions to enable data for distribution, processing, and analysis and participate in one or more of those activities directly.

But in either case, you are an expert. You need to ask the right questions and set the right expectations as you approach the technical aspects of data. It is not always possible to get the best solution with the following questions, but they will help you get started and eliminate the mismatches easily right off the bat:

• Volume and scalability: Volume is the amount of data you need to store and process in a given period. Scalability is how much you expect your data to grow in the foreseeable future. Here are some questions you can ask in this area:
  • What is the size of data you are going to be dealing with at the time of design and at each stage in the life cycle of the data?
  • How much do you expect it to scale with time?
• Velocity: This is the rate at which data is transferred or processed by your application. Some questions that you can ask concerning the velocity of data are as follows:
  • What is the rate/schedule at which the data needs to be sent and processed?
  • If you are ingesting, processing, and writing into storage, do you need to match the velocity at each of these stages?
• Veracity: Veracity is the amount of variation you can expect in the data structure and attributes:
  • What variation is expected to be seen in the incoming data?
• Security: Access control, encryption, and security are key considerations at the database design stage:
  • How much access restriction (row-level, object-level, and fine-grained levels of access control), encryption, privacy, and compliance does your data need?
  • What kind of encryption is required for the data?
  • Do you need to design views based on the type of data and access control required for your data?

  Other common areas of design consideration are availability, resilience, reliability, and portability.

• Data retrieval: Data can be retrieved in many different modes, depending on your use case. The design aspects to keep in mind concerning retrieval are as follows:
  • What is the volume of the data being read?
  • What is the volume of the data being written?
  • What is the frequency of reads?
  • What is the frequency of writes?

This is a key technical aspect to address in design because when it’s not done in design, often, engineers and architects are posed with performance challenges and go back to assessing their foundational architecture and configuration at a much-matured stage in development.

Having assessed all these questions and considerations, the logical next step is to choose from/eliminate from the database types out there, predominantly focusing on the structured and less structured types of data:

• Relational databases:
  • Online transaction processing (OLTP)
  • Online analytical processing (OLAP)
• NoSQL databases:
  • Document database
  • Key-value database
  • Wide-column database
  • Graph database

Let’s look at these different categories. We will see some examples of some of these categories in the next chapter.

Relational database

The first category we are going to look at is the relational database. There are two broad categories of relational databases: OLTP and OLAP systems.

Online transaction processing

We have the good old relational database for OLTP, which typically follows these normalization rules:

1. First normal form: Each column in the table has atomic value, contains no duplicates, and contains a primary key, which is one or more ordered columns that uniquely identify a row.
2. Second normal form: A relation is in the second normal form if the first normal form is satisfied and a separate table is used to store all the values that apply to multiple rows and linked using foreign keys. A foreign key is one or more ordered columns that relate to a primary key in another table.
3. Third normal form: A relation is in the third normal form if the second normal form is satisfied and if the column that is transitively dependent on the primary key should be eliminated and moved to another table, along with the determinant. So, if attribute A is the primary key, A is dependent on B, and B is dependent on C, then A to C form transitive dependency. So, the dependency between B and C should be moved to a different table.

Transactional structured data is usually operated one row at a time. For example, consider an application where you are looking up employee information. In this case, you will need a lot of attributes (columns) from a single row. So, it is efficient to store all row attributes together and retrieve them in a block. This is what is called row-oriented storage.

Online analytical processing

OLAP is typically used for data mart and data warehouse applications. This type requires Structured Query Language (SQL) to define, manipulate, query, and manage. They usually facilitate the following:

• Aggregations and roll-ups
• Drill down
• Pivots
• Slicing and dicing

For example, imagine a scenario where your business or data analyst needs to retrieve reporting summaries such as total employees joined in the last month and total cost to the company incurred. In this case, you will only query three columns. So, for all the rows selected, instead of retrieving all the columns, it makes sense to only retrieve three. This is a common pattern in analytical applications, and they use a column-oriented storage mechanism.

NoSQL database

There are NoSQL databases for semi-structured data – that is, data less structured than fully structured tabular data. There are a few types of NoSQL databases. There is no formal model or normalization requirement for this type.

Sometimes, it’s misleading when we hear that NoSQL options are schema-less. NoSQL options may not have a schema in the same pattern as relational databases, but they do have a structure to represent data. Four broad types of NoSQL databases are loosely based on a specific way of storing data. Let’s look at the logic for a data model in each case.

Document database

A document database stores data in document format, similar to a JSON document. Each document stores pairs of fields and values, with a wide variety of data types and data structures used as values. Document databases allow you to keep adding attributes as needed without making changes to the database schema each time.

For example, consider the scenario where you cannot fix all the attributes at the design stage and have to add more attributes as the process evolves, such as in the case of a retail store selling electronic equipment where a laptop has memory, a processor, a charger, and other configuration attributes and a washing machine has another set of attributes, such as weight, power, length, width, and so on. To search efficiently by these attributes, document databases allow for indexes. If you want to search an equipment by length, then you can create an index on length.

Key-value database

The key-value model consists of a key and a value, and it is the simplest type of database in terms of understanding and usage. The data model has two parts: data and a string associated with the data. Data can be accessed via direct request (send the key and get the data) rather than using any kind of query language. Key-value databases are simple if you have to search only on your key. However, if your value-data structure is complex, for example, a JSON object is stored as a value, and if you want to be able to search on items within the JSON structure, then a key-value database is not the best option. In that case, a document database would be a better option.

The following table is an example of a key-value database:

Table 1.1 – Key-value example table

Let’s discuss key-value databases next.

Wide-column database

Wide-column databases follow a table form structure that is both flexible and scalable. Each row has a key and one or more associated columns, called column families. Each row’s key-column family is allowed to have as many numbers of columns and the columns can have as many kinds of data as possible. Data is accessed using a query language. This type of column structure allows for faster summary (aggregation) queries. Wide-column databases are designed in such a way that they respond to specific queries. Instead of using indexes to allow efficient lookup of rows, wide-column databases store data in such a way that rows with similar keys (row keys are like primary keys in relational databases) are close together.

For example, consider log data from an application. The following table represents data that is organized to answer queries looked up by event ID and then time:

Table 1.2 – Table organized by Event ID and Time

The preceding table is not applicable for querying events in the past hour because it is not organized by time first. The following table, on the other hand, is organized to answer queries by time range. Note that a new table with the desired schema needs to be created to accomplish this. Wide-column databases do not use indexes for queries:

Table 1.3 – Table organized by Time and Event ID

Let’s move on to graph database next.

Graph database

Graph databases consist of nodes and edges. Nodes are connected by edges. Data is stored in the nodes and information about how the nodes are related is stored in the edges. Node and edge data is typically retrieved using query languages, Graph Query Language (GQL), and sometimes SQL as well. There are two types of query methods we can use to retrieve data from graph databases:

• Cypher Query Language, which specifies SQL-like statements that describe patterns in a graph
• Traversal Language, which specifies how to navigate from one node to another in the graph

People and connections are a great example of a use case for graph databases. A person could be designed as the node and the relationship of that person with other persons could be the links, also known as edges.

In the following diagram, persons 1, 2, 3, 4, 5, and 6 are all people and the link between them denotes friends:

Figure 1.2 – Representation of nodes and edges in a graph database

We will look into some of these categories in detail later in this book. For now, let’s summarize what we’ve learned in this chapter.

Data and databases form the core of any system, product, process, application, and even idea these days. Also, there are a lot of them lately. So, choosing the one that fits your needs and adds value, precision, cost-effectiveness, and representation to your information is paramount. This chapter aimed to point you in the direction of the right questions to ask at the time of design and modeling. As you possibly already know, almost always drive business with technology and not the other way around. Make the right database choices and data design decisions in a way that works for you.

In the upcoming chapters, we will look into some of these categories in detail with hands-on examples while also providing how-to guides and best practices. Before that, we will look at cloud computing and basics of handling data on cloud in the next chapter.