An Introduction to the Four Modeling Types

The previous chapter introduced the concept of a model as a selective simplification of reality. Like Harry Beck’s Tube map, designed expressly to navigate London’s subway system, other map variants—such as street and topographical—exist to describe different aspects of geography. The same applies to databases and the organizations that rely on them to enable their business operations and analytics.

Many people think of modeling as simply documenting a database—diagramming. But modeling goes way beyond tables and databases by not only helping the developers understand the business but also helping the business understand itself.

Organizations use different models and modeling styles from org charts to network diagrams to navigate their many complexities. None of these provide a perfect map, but some serve as the right map for the right job. This chapter will explore the modeling types used to map and...

Design and process

Over the years, many books have been written about modeling and designing database systems. However, many of these books fail to make the connection between technicality and practicality—which are inextricably linked. Often, modeling books prioritize completeness—such as describing four different notations to express a relationship—and ignore usability (e.g., which notation would be most accessible to business users?).

The most significant complexity of a database system is not technical. It is the business model itself—the interactions within a business and the rules that govern them. If the business model is not aligned with the data model, the database will be repeatedly forced to adjust (while losing organizational trust and resources in the process.)

What is needed is a business-readable language that developers use to build and document the database landscape.

Ubiquitous modeling

Before defining data structures, we need to understand the business model that generates them. Such a model will make it possible to build a database system aligned with business processes and able to anticipate change. Like seeing a forest for the trees, ubiquitous modeling allows us to see the business for the data.

A business has many layers: from a mission statement that defines a company ethos, to the sales transactions, to the logistics that support them, to the data, metadata, analysis, and more. However, when most people think of a model, they tend to focus only on the data layer—forgetting that, in isolation, the data only tells part of the story.

In reality, the modeling process involves teams across the entire organization, including management, business departments, data teams, and analysts. Everyone in the organization, no matter their technical background or domain expertise, will work with modeling in some capacity (even if it is in the...

Conceptual

Modeling begins long before databases—or even data—enter the picture; it starts with the business itself. A conceptual diagram should be as valid for describing the organization’s operating model as it would be for laying the foundations of its database landscape.

What it is

Conceptual modeling is a process of identifying and visually mapping the moving pieces, or entities, of a business operation. Before going further, let’s establish what an entity is.

Entity: A person, object, place, event, or concept relevant to the business for which an organization wants to maintain information. Examples of entities common to many companies include employee, customer, sale, and item. Entities are typically referenced in the singular and represent the class or type of an object (more on this and the singular versus plural naming debate in Chapter 10, Database Naming and Structure). Entity instances are occurrences of such a class or type:

...

Logical

Once the building blocks of the business model have been identified through conceptual modeling (entities, attributes, and relationships), logical modeling begins. However, there is no strict distinction between the elements used in logical and conceptual designs. In fact, many database design textbooks do not differentiate between the two, combining them into a single style and tackling them in a single step.

This book takes a different approach—distinguishing between conceptual and logical modeling, not due to the elements or their notation but due to the natural flow of the design process. Because database textbooks are geared towards a technical audience, many lose sight of the less technical participants of database modeling: the business users.

Although a logical model can express everything that a conceptual one can, it also includes a great deal of technical detail, which may alienate those team members who lack the foundation to make sense of it. In the...

Physical modeling

Chapter 1, Unlocking the Power of Modeling separated the many meanings and applications of the word model, noting how it is used for the physical structure of database tables and the accompanying visual diagram. A physical model contains all the information necessary to construct and deploy the design to a specific database (e.g., Snowflake).

This also works in the opposite direction; many modeling tools and SQL integrated development environments (IDEs) can reverse engineer diagrams directly from the database data definition language (DDL). Reverse engineering lets users generate visual representations of the underlying schema on the fly to help them explore relationships.

How exactly does this conversion work?

What it is

The physical model is a blueprint for deploying a database to Snowflake and the map that holds the technical and operational links that help users navigate it.

The transition from logical to physical modeling is largely systematic...

Transformational

It all begins with SELECT. Modeling through transformational logic is a powerful and highly maneuverable method for modeling data that comes with one serious drawback: it needs existing data to SELECT from. Transformational modeling is rarely done in transactional databases because, in such systems, data is created and modified through the company’s operational processes (e.g., purchases and profile updates)—with which expensive transformational processes should not compete for critical system resources. However, in data warehouses, where conformed datasets are extracted and duplicated with fresh timestamps for each load, transformational modeling becomes a necessity.

Because transformational modeling selects from existing structured data, the result set is already structured. Selecting the SUPERHERO_NAME and HAS_MASK columns and creating a table will preserve their structure (VARCHAR and BOOLEAN, respectively). However, as with all modeling, transformations...

Summary

As we have seen throughout this chapter, modeling is a process used to agree, plan, and develop a database design and a means to navigate and explore it to gain greater insight into the underlying business context. Every new project must pass through the four modeling stages, whether formally recognized or not. But even for existing databases that have not been adequately documented, reverse engineering is an effective mechanism to work backward from a database to uncover its business meaning.

The design journey starts with conceptual modeling—a collaborative process that involves business and data teams working together to understand the core elements that underpin business operations and how they interact. Conceptual models favor simplicity over detail—making them accessible to team members of all backgrounds and helping steer conversations to converge at a shared understanding of the business model.

After conceptual modeling, the data team can add further...

Further reading

Effective communication between technical and business teams is notoriously challenging. In The Rosedata Stone, Steve Hoberman presents a guide for creating a precise diagram of business terms within your projects. The business terms model is a simple yet powerful communication tool to help bridge the technical knowledge gap and meet business teams on their own terms.

• Hoberman, Steve. The Rosedata Stone: Achieving a Common Business Language Using the Business Terms Model. Technics Publications, 2020.