DEV Community

Cover image for DATA MODELLING
Nicholas Kipngeno
Nicholas Kipngeno

Posted on

DATA MODELLING

What is Data Modelling?

Data modelling is the process of defining how data is stored, related, and organized within a database. It involves designing tables, relationships, keys, and constraints to ensure the data structure supports business needs and performance requirements.

Keys in Data Modelling

✅ Primary Key

A primary key is a column (or combination of columns) that uniquely identifies each record in a table. For example, CustomerID in the Customers table ensures that no two customers are duplicated.

🔗 Foreign Key

A foreign key is a reference to a primary key in another table. It establishes relationships between tables. For example, CustomerID in the Orders table links each order to the correct customer.

🧩 Composite Key

A composite key is made up of two or more columns to uniquely identify a row. For instance, in an OrderItems table, the combination of OrderID and ProductID could act as a composite key to ensure uniqueness of each line item in an order.

🔄 Normalization vs Denormalization

📘 Normalization

Normalization is the process of structuring a relational database to minimize redundancy and improve data integrity. It usually involves splitting large tables into smaller ones and using foreign keys to connect them.

Benefits:

  • Reduces data duplication
  • Easier to maintain consistency
  • Smaller storage footprint

Drawback:

Complex queries requiring joins

📕 Denormalization

Denormalization intentionally introduces redundancy to reduce query complexity and improve read performance, especially in analytical systems.

Benefits:

  • Faster read times
  • Simplified queries for reporting
    Drawback:

  • Risk of data inconsistency

  • Larger storage usage

⭐ Star Schema: Denormalization for Analytics

In data warehousing, star schema is a common denormalized model that makes querying large datasets efficient. It consists of:

  • A central fact table (e.g., FactSales) that holds measurable data like sales.
  • Multiple dimension tables (e.g., DimProduct, DimCustomer) that provide descriptive context.

This model enables slicing, dicing, and fast reporting—ideal for business intelligence tools.

Top comments (0)