Data Modelling Cheatsheet

Cheatsheet Content

### Introduction to Data Modelling Data modelling is the process of creating a visual representation or blueprint of an information system's data. It defines the structure, relationships, and constraints of data within a system. #### Why Data Modelling? - **Improved Data Quality:** Ensures consistency and accuracy. - **Better Communication:** Provides a common language between business and IT. - **Reduced Development Time:** Guides database design and application development. - **Enhanced Performance:** Optimizes database structures for efficient querying. - **Easier Maintenance:** Simplifies future changes and scalability. ### Conceptual Data Model (CDM) - **Purpose:** High-level, business-oriented view of data. Focuses on "what" the system contains. - **Audience:** Business stakeholders, data architects. - **Key Components:** - **Entities:** Real-world objects or concepts (e.g., Customer, Product, Order). - **Attributes:** Characteristics of entities (e.g., CustomerID, CustomerName). - **Relationships:** Associations between entities (e.g., a Customer places an Order). - **Notation:** Often uses Crow's Foot or UML. - **Example:** - CUSTOMER (CustomerID, Name, Address) - ORDER (OrderID, OrderDate, CustomerID) - Relationship: CUSTOMER "places" ORDER (1-to-many) ### Logical Data Model (LDM) - **Purpose:** More detailed than CDM, defines data structure without specific database technology. Focuses on "how" data should be structured. - **Audience:** Data architects, database designers. - **Key Components:** - **Entities:** Translated into tables. - **Attributes:** Translated into columns with data types (e.g., VARCHAR, INT). - **Primary Keys (PK):** Uniquely identifies each record in a table. - **Foreign Keys (FK):** Establishes relationships between tables, referencing a PK in another table. - **Relationships:** Defined by PK-FK relationships. - **Normalization:** Process of organizing data to reduce redundancy and improve data integrity (1NF, 2NF, 3NF, BCNF). - **Normalization Forms:** - **1NF:** Eliminate repeating groups, create separate tables for related data, identify PK. - **2NF:** Meet 1NF, eliminate partial dependencies (non-key attributes dependent on only part of a composite PK). - **3NF:** Meet 2NF, eliminate transitive dependencies (non-key attributes dependent on other non-key attributes). - **BCNF (Boyce-Codd Normal Form):** Stricter than 3NF, every determinant is a candidate key. ### Physical Data Model (PDM) - **Purpose:** Technology-specific implementation of the LDM. Focuses on "how" data is stored in a specific database system. - **Audience:** Database administrators, developers. - **Key Components:** - **Tables:** Specific names (e.g., `tbl_customers`). - **Columns:** Specific data types (e.g., `NVARCHAR(255)`, `INT`, `DATETIME`). - **Indexes:** Improve query performance. - **Constraints:** Primary Key, Foreign Key, Unique, Not Null, Check. - **Views, Stored Procedures, Triggers:** Database objects. - **Denormalization:** Intentionally introducing redundancy to improve read performance for specific queries. - **Example (SQL Server):** ```sql CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, Email NVARCHAR(100) UNIQUE, RegistrationDate DATETIME DEFAULT GETDATE() ); CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATETIME NOT NULL, CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID), TotalAmount DECIMAL(10, 2) ); ``` ### Data Modelling Techniques #### 1. Relational Modelling - **Concept:** Data stored in tables (relations) with rows and columns. Relationships established via PK/FK. - **Strengths:** High data integrity, widely supported, flexible query capabilities (SQL). - **Weaknesses:** Can be complex for highly interconnected data, performance issues with many joins. - **Use Cases:** OLTP (Online Transaction Processing) systems, traditional databases. #### 2. Dimensional Modelling (Star Schema, Snowflake Schema) - **Concept:** Optimized for querying and reporting, especially in data warehouses. - **Fact Tables:** Contain measures (numeric values) and foreign keys to dimension tables. - **Dimension Tables:** Contain descriptive attributes about the business process. - **Star Schema:** A fact table in the middle, surrounded by denormalized dimension tables. - **Snowflake Schema:** Similar to star, but dimension tables are normalized into sub-dimensions. - **Strengths:** Excellent query performance for analytical queries, easy for business users to understand. - **Weaknesses:** Not ideal for OLTP, requires ETL processes. - **Use Cases:** Data Warehouses, Business Intelligence (BI), OLAP (Online Analytical Processing). #### 3. Entity-Relationship (ER) Modelling - **Concept:** Graphical representation of entities and their relationships. Foundation for relational models. - **Components:** Entities, Attributes, Relationships (1:1, 1:N, N:M). - **Strengths:** Intuitive, good for initial design, widely understood. - **Weaknesses:** Can become complex for very large systems. - **Use Cases:** Conceptual and Logical Data Modelling. #### 4. Object-Oriented Modelling - **Concept:** Data and behavior are encapsulated into objects. - **Strengths:** Maps well to object-oriented programming languages. - **Weaknesses:** Less common for pure database design. - **Use Cases:** Object databases, specific application domains. #### 5. Graph Modelling - **Concept:** Data represented as nodes (entities) and edges (relationships) in a graph structure. - **Strengths:** Excellent for highly interconnected data, flexible schema. - **Weaknesses:** Different query languages (e.g., Cypher), less mature tool ecosystem than relational. - **Use Cases:** Social networks, recommendation engines, fraud detection. ### Data Modelling Best Practices - **Understand Business Requirements:** Start with what the business needs. - **Involve Stakeholders:** Collaborate with business users and technical teams. - **Iterative Process:** Data models evolve; start simple and refine. - **Documentation:** Document entities, attributes, relationships, business rules, and data definitions. - **Consistency:** Use consistent naming conventions. - **Balance Normalization/Denormalization:** Optimize for integrity (normalization) or performance (denormalization) based on use case (OLTP vs. OLAP). - **Data Governance:** Establish policies for data ownership, quality, and security. ### Common Challenges - **Scope Creep:** Expanding requirements leading to complex models. - **Lack of Business Understanding:** Models not aligning with actual business processes. - **Performance Issues:** Poorly designed models leading to slow queries. - **Data Redundancy & Inconsistency:** Lack of proper normalization. - **Integration with Existing Systems:** Challenges in combining data from disparate sources. - **Evolving Requirements:** Models need to adapt to changing business needs. ### Conclusion Data modelling is a foundational skill for anyone working with data. By systematically designing data structures, we ensure data quality, improve system performance, and facilitate better decision-making. Mastering the different stages and techniques allows for the creation of robust and scalable data solutions. ### Author Amol Patil, Data Engineer at Hoonartek LinkedIn: [https://www.linkedin.com/in/amol-patil-2072aa167?utm_source=share&utm_campaign=share_via&utm_content=profile&utm_medium=android_app](https://www.linkedin.com/in/amol-patil-2072aa167?utm_source=share&utm_campaign=share_via&utm_content=profile&utm_medium=android_app)