data modeling is an important part of data architecture. it means making a plan for how data will be organized, stored, and used in a system. this chapter looks at different ways to model data, helping you learn how to create useful data models for your organization.

table of content

in this chapter, we will cover the following topics related to data modeling:

what is data modeling?
relational modeling
dimensional modeling
denormalization in dimensional modeling
common data model in data modeling
data vault modeling
kimball and inmon data warehousing methodologies
hybrid models
choosing the right data model
common myths about inmon and kimball methods

what is data modeling?

data modeling is an important technique for designing databases. it includes figuring out what data needs to be stored and organizing it into tables and columns to show how different pieces of data relate to each other. this structure can be applied to different types of databases, including relational and nosql.

relational modeling

relational modeling was introduced by edgar f. codd in 1970. it organizes data into tables(relations), where each table contains rows for records and columns for attributes. this method uses keys to show how tables are related to each other, helping to maintain data accuracy and making it easier to retrieve information.

keys in relational modeling

in relational modeling, keys are important for keeping the database organized and accurate. they help in identifying each record uniquely and show how different tables are connected. here are some key types.

primary key: this is a unique identifier for each record in a table, like studentid in a students table.
foreign key: a foreign key is a column in one table that links to the primary key in another table, creating a relationship. for example, productkey in a sales table connects to a product in a products table.
natural key: a unique field that already exists and is used as a primary key, like a social security number(a government-issued number that identifies individuals).

entity-relationship diagrams

you usually start relational modeling with an entity-relationship (er) diagram. this diagram shows the different parts of the database (entities) and how they are connected (relationships). after finishing the er diagram, you can create a more detailed model that includes the actual tables and columns of the database.

this is how you can create an er diagram.

in the above er diagram, we represent the student and course entities. here's a brief explanation of what we did:

entity: the diagram includes two main entities: student and course.
attributes: the diagram lists attributes for both the student and course entities. the student entity has attributes such as:
- studentid (a unique identifier)
- name (composite: first name, middle name, last name)
- phone numbers (a multi-valued attribute)
the course entity includes:
- cou_id (a unique identifier)
- name (the name of the course)
weak entity: grade is a weak entity that depends on both the student and course entities.
relationships:the enrolls relationship connects student and course, meaning one student can enroll in many courses. the earns relationship links student and grade, showing that one student can receive multiple grades.
key attributes in relationships:
- the enrolls relationship may include enrollment date and status.
- the earns relationship may include grade date.
composite and multi-valued attributes: the name attribute of the student entity is composite, while phone numbers is a multi-valued attribute.

er diagram components

this diagram includes several components that show how entities relate to each other. below are the key components used in creating the er diagram.

normalization rules for data integrity

after creating the er diagram, you apply normalization. normalization is a process that takes complex databases and breaks them down into simpler tables. this helps reduce duplicate data and improves the accuracy of the information. the process includes several steps known as normal forms:

first normal forms(1nf): this form makes sure that the table is organized properly. it requires that:
- the table has a primary key.
- each column has only one value.
- there are no repeating groups of columns.
second normal form (2nf): building on 1nf, this form makes sure that:
- it meets all the conditions of 1nf.
- every non-key column depends only on the primary key.
third normal form (3nf): building on 1nf, this form makes sure that:
- it meets all the conditions of 1nf and 2nf.
- non-key columns must connect directly to the primary key, not through another column.

tracking changes with history tables

in relational databases, it's important to track changes over time. to do this, we use history tables. these tables are copies of the original tables, but they have extra columns that record important details, such as:

timestamp: when the change occurred.
user id: when the change occurred.
before/after values: the values before the change and the new values after the change.

history tables are very helpful for auditing, reporting, and recovering data. they give you a complete picture of how your data has changed over time.

dimensional modeling

dimensional modeling began in 1996 to make it easier to analyze and query data. it divides data into two parts: facts(which are the numbers or data points) and dimensions(which provide context, like categories or dates). this method is helpful when regular databases become slow or difficult to use. it usually takes data from these traditional databases to create a more efficient way to analyze information.

facts in dimensional modeling

in dimensional modeling, "facts" are numerical data points that measure specific metrics, such as sales or revenue. these facts can be combined or summarized, like finding averages or totals, which makes it easier to analyze the data. this way, you can quickly discover useful insights from large sets of information.

dimensions in dimensional modeling

in dimensional modeling, "dimensions" help explain the facts. they describe important details about the data, such as time, products, or customer information. dimensions are often organized into hierarchies, which allows for deeper data analysis. fact tables store the numerical data, while dimension tables keep the related details that describe this data.

keys in dimensional modeling

in dimensional modeling, we usually use surrogate keys as primary keys instead of natural keys. surrogate keys are artificial values created specifically to uniquely identify records in a database and are helpful when natural keys are not available or suitable.

natural keys can provide meaningful information, but they have some downsides.

they can be longer and more complicated.
they might contain sensitive information, which can raise privacy issues.
they can cause duplication or inconsistencies when combining data from different systems.

tracking changes in dimensional modeling

in dimensional modeling, we track changes in data using slowly changing dimensions (scds), which help manage how data changes over time. there are three types.

type 1: this type replaces old data with new data. it's used for small changes where we don't need to keep the old information, such as correcting a phone number.
type 2: this type keeps both the new and old versions of the data. it's useful for tracking changes over time, like when customers move to new addresses, ensuring that we have accurate historical information.
type 3: this type creates a new record for each change, keeping a full history of the data. while it's more complex, it allows us to keep detailed records of all changes.

history tables vs. slowly changing dimensions

history tables and slowly changing dimensions(scds) both track changes in data, but they do so in different ways.

history tables: these tables keep a complete history of changes for each individual record. they track every version of a record over time, allowing you to see all the changes made to a specific entry.
slowly changing dimensions (scds): slowly changing dimensions focus on managing changes within dimension tables, which store detailed information about the data. they allow for clear and structured updates using methods like type 1, type 2, and type 3. scds ensure that historical data stays accurate and reliable, making it easier to analyze trends over time.

history tables give a full view of all changes for each record, while slowly changing dimensions focus on how dimensional data changes over time. this way, we can be sure that historical information is correct and easy to find.

denormalization in dimensional modeling

denormalization means copying data into multiple tables in dimensional modeling. this simplifies the database by reducing the number of tables and the number of joins needed, which makes queries run faster and helps you create reports more easily.

however, keeping this copied data consistent can be tough. for example, if a category name changes, it has to be updated in several places in the dimensional model. in a relational model, you only update it once, which helps prevent mistakes.

comparing dimensional and relational models

in data management, dimensional models and relational models are two different approaches. dimensional models are designed to make it easier to analyze data, while relational models focus on organizing data accurately.

dimensional models:
- these models are good for analyzing data and understanding business performance.
- they can handle many tables efficiently, speeding up queries and making reporting easier.
relational models:
- these models are easier to set up if the data is organized in standard tables with rows and columns.
- they focus on keeping data correct and reducing duplication, but this can slow down analysis.

dimensional modeling uses different layouts, like star and snowflake schemas. a star schema has one main table surrounded by other tables, making it clear and simple to understand.

dimensional models are better for analyzing data, while relational models provide a clear way to manage data. the choice between them depends on what the business needs and how complex the data is.

common data model in data modeling

a common data model(cdm) is a standard method for storing and organizing data, particularly in data warehouses. it creates a clear and consistent way to represent data in tables, making it easier for different systems to understand and work with the information.

when importing data from different sources, like various customer relationship management (crm) systems, it's not practical to use just one system's format. each system may have different table structures and field names, which can cause confusion. instead, you should create a new common data model (cdm) that brings all these formats together into one clear structure. this common data model (cdm) will be flexible enough to meet your current and future data needs. many cloud providers offer easy-to-customize cdms for specific industries, helping you save time and reduce risks in data integration.

data vault modeling

data vault modeling is a way to organize data, developed by daniel linstedt in 2000. it provides a flexible and reliable way to manage historical data, making it easier to keep track of changes over time.

a data vault model has three main parts:

hubs: these are tables that represent important business ideas, like customers or products, with unique ids.
links: these tables show how hubs are related, using keys that connect to the hubs' ids.
satellites: these hold extra details about the hubs or links, like changes over time, and connect through keys.

disadvantages of data vault

while data vault offers flexibility and a clear way to manage historical data, it also comes with some challenges that can impact how well it works and how easy it is to set up.

complexity: setting up a data vault can be complicated and requires skilled professionals to manage it.
data duplication: storing detailed data can result in duplicate entries, which can increase storage costs.
performance: having many tables can slow down data retrieval, making queries more complicated.
lack of standardization: because it's a newer method, it might be hard to find engineers who are experienced in data vault.

kimball and inmon data warehousing methodologies

this section looks at two main ways to build data warehouses: the inmon method and the kimball method. each has its own benefits and challenges, and the right choice depends on what your organization needs.

inmon's top-down methodology

the inmon method starts with a central data warehouse that collects information from different sources. after that, smaller data marts are created for specific departments using data from the central warehouse. this structured approach is often preferred by large organizations that focus on data quality.

the process in inmon's method:

data staging: first, data is quickly collected from different sources into temporary tables without any changes.
corporate information factory (cif): next, the data is organized in a central location, serving as the main source of truth for the organization.
dependent data marts: after the cif is set up, smaller data marts are created for different departments, using the central data for their information.

advantages: this method helps maintain high data quality and works well for larger organizations with complex data needs.

challenges: it can lead to data duplication, which increases storage costs and makes maintenance more difficult.

kimball's bottom-up method

the kimball method starts by collecting raw data into temporary tables called staging tables without cleaning it. this method focuses on creating independent data marts first, which are designed to meet the specific needs of different business areas. these data marts are then connected to provide a complete view of the data.

the process in kimball's method:

independent data marts: each data mart is built for a specific department, using simple models to make analysis easier.
integration: data marts are linked through common dimensions, ensuring that all areas stay consistent.
cubes for reporting: data can be organized into cubes, which makes it easy to look at data from different angles. this helps you quickly find important insights.

advantages: this method encourages users to participate, which leads to better data management. it also reduces the chances of having the same data stored multiple times.

challenges: if not managed well, there can be differences between the data marts, which may cause confusion and inaccurate reports.

differences between inmon and kimball

this section shows the key differences between inmon'sand kimball's methods, focusing on how each meets different needs.

starting point: inmon starts by creating a central data warehouse that collects data from different sources. in contrast, kimball focuses on building separate data marts from the beginning, targeting the specific needs of different departments.
structure: inmon highlights a physical central data warehouse, which acts as a single source of truth for the organization. kimball, however, allows for more flexibility, letting organizations create data marts without needing a strict central warehouse.
adaptability: both inmon and kimball can adapt to modern data practices, such as using data lakes. this flexibility helps organizations keep up with changing data needs and makes it easier to manage various data sources.

these differences help organizations choose the approach that best fits their needs and goals in managing data.

hybrid models

a hybrid model combines parts of both kimball and inmon methods. it starts by gathering raw data from various online transaction processing (oltp) systems into temporary holding tables without cleaning it.

then, the data is moved to a corporate information factory (cif), where it is stored in a detailed format. after that, the data is transferred to independent dimensional data marts, which can hold both detailed and summarized data based on what each department needs.

some data can also be put into cubes for reporting. using cubes is recommended because they:

serve as a clear interface.
support multiple users at the same time.
provide summarized data for better speed.
eliminate complicated joins.
include hierarchies and key performance indicators (kpis).
ensure data privacy with row-level security.
allow for advanced time calculations for trend analysis.

using database views in the extract, transform, load (etl) process can make coding easier and improve querying. views are virtual tables based on sql queries, helping to manage complexity without storing extra data.

mirrored oltp systems

a hybrid model involves a mirrored online transaction processing (oltp) system, which is a duplicate that operates alongside the original system. this arrangement allows the main oltp to focus on user access and maintenance while data is being collected. it also simplifies the extract, transform, load (etl) processes and can boost performance by allowing specific indexing without affecting the original system.

choosing the right data model

there's no one-size-fits-all approach when it comes to choosing a data model. your decision should consider factors like security, data size, and performance. for example, adding star schemas can improve query performance.

common myths about inmon and kimball methods

here are some common misconceptions about the inmon and kimball data methods:

kimball is purely bottom-up: in fact, kimball combines top-down planning with bottom-up execution, focusing on both design and implementation.
inmon requires a lot of upfront design: inmon supports a gradual approach to building data warehouses instead of trying to do everything at once.
inmon doesn't support star schemas: in reality, inmon recognizes the benefits of star schema data marts for easier access by users.
few companies use inmon's method: surveys show that many organizations actually prefer the inmon approach.
the methods are incompatible: kimball and inmon can work well together, especially in hybrid models.

kimball's method includes project planning and maintenance, while inmon focuses on the data warehouse itself. when people talk about kimball, they often mean dimensional modeling, which includes techniques like conformed dimensions and snapshot fact tables.

Save or share this article