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)