Data Modeling
Data modeling is the process of creating a structured and visual representation of data, defining how data elements relate to one another within a system. It helps translate business requirements into organized data structures that support accurate analysis and effective decision-making.
What is a Data Model?
A data model is a visual and logical representation of an organization data elements and the relationships between them.
Importance of Data Modeling
Improved data organization.
Better data integrity.
Enhanced data security.
Increased scalability.
Types of Data Models.
There are three main types of data models:
- Conceptual data models.
They are also referred to as domain models and offer a high-level view of what the system will contain, how it will be organized, and which business rules are involved. Conceptual models are created as part of the process of gathering initial project requirements.
Purpose: Organize and define business problems, rules and concepts.
Focus: High-level overview of data such as customer data, market data and purchase data.
Audience: Business stakeholders and analysts.
Example Use: Understanding the relationship between customers and their orders before designing the database structure.
- Logical data models.
Shows how data entities are related and describes the data from a technical perspective.
Purpose: Define tables, columns, relationships and constraints that form the data structure.
Focus: Structure of the data without depending on any specific database management system (DBMS).
Audience: Data architects and analysts.
Example Use: Outlining the schema, relationships and rules for customer and order data, which later guides the physical database design.
- Physical data models.
Guides the implementation of a database with a detailed representation of a database design.
Purpose: Define every element needed to construct a database, including tables, columns, keys and constraints (primary key, foreign key, NOT NULL, etc.).
Focus: Actual implementation of the database using queries and the chosen DBMS features.
Audience: Developers and database administrators (DBAs).
Example Use: Creating the database schema and ensuring that all constraints and relationships are enforced in the physical database.
Data Modeling Techniques
Here we describe the primary techniques used to develop data models.
- Hierarchical data models: Represent one-to-many relationships in a treelike format. In this type of model, each record has a single root or parent, which maps to one or more child tables.
- Entity-relationship (ER) models: Are a variation of relational models and they provide a graphical representation of entities, their attributes, and the relationships between them.
Relational Model: Relational Mode represent the links between tables by representing data as rows and columns in tables. It is frequently utilised in database design and is strongly related to relational database management systems (RDBMS).
Object-oriented data models: Models are similar to ER models in that they represent data as objects with attributes and methods, but object-oriented models abstract entities into objects.
Dimensional data models: Models are based on the concept of dimensions, which are used to categorize and organize data, and facts, which are the numeric measurements or values associated with the dimensions.
Ralationship in a Database
A relationship in a DBMS exists when a variable has a connection with the properties stored in different tables. Such relationships help the organization of entities intertwined with each other, ultimately enabling efficient data processing. They're exhibited usually via keys in a table, which is either columns or fields that specify a distinctive arrangement for each record.
Key features of database relationships.
Primary keys: Unique identifiers for each record in a table, ensuring that no two rows are identical.
Foreign keys: Links between tables, pointing to primary keys in another table to establish relationships.
Data integrity: Relationships ensure consistency and prevent errors, such as associating an order with a non-existent customer.
Types of Database Relationships.
Database relationships can be broadly categorized into four types, each serving a specific purpose in connecting data.
- One-to-one (1:1) relationships
A one-to-one relationship connects one record in a table to exactly one record in another table. This is often used when splitting data into multiple tables for better organization or security.
- One-to-many (1:N) or many-to-one (N:1) relationships
In a one-to-many relationship, a single record in one table can be associated with multiple records in another table. This is one of the most common relationship types in relational databases.
- Many-to-many (N: N) relationships
A many-to-many relationship occurs when multiple records in one table are related to multiple records in another table. This is implemented using a junction table to manage the connections.
- Self-referencing relationships
In a self-referencing relationship, records in a table are linked to other records within the same table.
Database Schemas
A database schema defines the structure and organization of data within a database. It outlines how data is logically stored, including the relationships between different tables and other database objects.
What is a Schema?
A schema is the blueprint or structure that defines how data is organized and stored in a database.
Types of Database Schemas
- Conceptual Database Schema
A conceptual schema describes the overall structure of the entire database at a high level.
It focuses on the meaning of data and captures how different entities relate to each other—without worrying about how data is stored physically.
- Physical Database Schema
The physical schema defines how the data is actually stored on disk.
Specifies files, indexes, partitions, storage blocks, access paths, etc.
It represents the lowest level of abstraction, focusing on performance, storage optimization, and data retrieval efficiency.
- Logical Database Schema
The logical schema describes the logical structure of data as it appears to the database designers and developers. Defines tables, columns, primary keys, foreign keys, relationships, and integrity rules. This schema represents a higher level of abstraction and is independent of physical storage details.
Difference between Logical and Physical Database Schema
Database Schema Designs
- Flat Model
A flat model schema is a 2-D array in which every column contains the same type of data/information and the elements with rows are related to each other. It is just like a table or a spreadsheet.
- Star Schema
The star schema is a simple and commonly used schema in data warehousing. It consists of a central fact table surrounded by dimension tables, forming a star-like pattern.
Use Cases: Star schemas are ideal for straightforward reporting and querying. They are efficient for read-heavy operations, making them suitable for dashboards and summary reports.
The star schema does not necessarily have to be in the shape of a star. Below is also a star schema and is a preferred arrangement for many experts as in this arrangement is easier to visualize filter propagation from dimension to fact tables.
- Snowflake Schema
The snowflake schema is a normalized version of the star schema. In this design, dimension tables are further divided into related tables, resulting in a more complex structure.
Use Cases: Snowflake schemas are used in scenarios requiring detailed data models and efficient storage. They are beneficial when dealing with large datasets where data redundancy needs to be minimized.
Joins in data modeling
Joins are used to combine data from multiple tables into a single view.
Types of Joins
- Inner Join
An inner join returns only the records that have matching values in both tables being joined. It combines rows from both tables based on a common column known as the join key. The result of an inner join will only include rows where there is a match in both tables.
- Left Outer Join
This type of join returns all the rows from the left table and matching rows from the right table. If there are no matching rows in the right table, it returns NULL values for the right table columns.
- Right Outer Join
This type of join returns all the rows from the right table and matching rows from the left table. If there are no matching rows in the left table, it returns NULL values for the left table columns.
- Full Outer Join
This type of join returns all the rows from both tables with NULL values in the columns where there is no match.
Summary
Here's the beginner-friendly recap:
Data Modeling is the process of organizing and connecting your data tables so Power BI knows how they relate to each other.
Schemas are the blueprint of your model. The Star Schema is your best friend in Power BI — one fact table surrounded by dimension tables. The Snowflake Schema exists but adds complexity without much benefit in Power BI.
Relationships are the lines between tables. They're built on Primary Keys and Foreign Keys, and they define how filters flow through your model. The most common type is One-to-Many (1:N).
Joins (like Inner Join and Left Join) are how Power BI physically combines rows from different tables when calculating your visuals.
Top comments (0)