Data Storage Repositories Data Storage & Design

🎧 Listen to more in-depth episodes on Spotify! Interactive Data Storage & Database Design

Data Storage Repositories

Organizations use various types of repositories to store and manage data, each serving a different purpose, from handling live transactions to enabling large-scale analysis.

Operational Data Store (ODS)

An interim repository for transactional data from multiple sources. Data is current and frequently overwritten.

Data Warehouse

A very large, centralized repository of historical and current data used for reporting and analysis, not transactions.

Data Mart

A subset of a data warehouse, focused on a specific business line or department, like marketing or sales.

Data Lake

A vast repository that holds both structured and unstructured data in its raw, natural format without a predefined schema.

Relational Database Design

Relational databases are the most common way to store structured data. They organize data into related tables to ensure completeness, eliminate redundancy, and enforce business rules.

Core Components of a Relational Database

Customers

🔑 CustomerID (PK)

FirstName

LastName

...

→

Sales Orders

🔑 SalesOrderID (PK)

🔗 CustomerID (FK)

OrderDate

...

Data is split into tables (e.g., Customers, Sales Orders). Each table has a Primary Key (PK) that uniquely identifies each record. A Foreign Key (FK) in one table links to the Primary Key in another, creating a relationship and avoiding data duplication.

Database Normalization

Normalization is a design technique that reduces data redundancy and improves data integrity. It involves a series of progressive rules known as Normal Forms. Click on each rule to understand its purpose.

The Rules of Normalization

1NF

First Normal Form

"The Key"

2NF

Second Normal Form

"The Whole Key"

3NF

Third Normal Form

"Nothing But The Key"

Click a rule above

The description of the selected normal form will appear here.

Data Warehouse Schemas

For data warehouses and data marts, data is often organized into specific schemas for easier reporting and analysis. The two most popular are the Star and Snowflake schemas. Click to compare them.

Dimensional Modeling: Star vs. Snowflake

Star Schema

Fact Table

Dim A

Dim B

Dim C

Snowflake Schema

Fact Table

Dim A

Dim B

Sub-Dim B1

Dim C

Click a schema above

The description of the selected schema will appear here.