Data Modeling
Data modeling defines the structure, relationships, and constraints that organize data within a system. It produces schemas — conceptual, logical, and physical — that determine how information is stored, queried, and maintained across databases and analytics platforms.
itData engineering and analytics | OpenSkills.info
Intro
Data Modeling
Data modeling turns real-world concepts and rules into a structure that a data system can store and use. You decide what each record represents, which facts belong together, how records relate, and which states the system must reject.
The central mental model is a set of promises about data. A model promises what a customer means, what makes an order unique, whether an order can exist without a customer, and what one sales row measures. Tables, documents, and diagrams express those promises. They do not create the meaning by themselves.
business questions and rules
|
v
concepts -> relationships -> constraints -> storage design
^ |
| v
stakeholder review <- observed use and change
A good model makes important assumptions visible before they become production defects. It gives developers, data professionals, and business stakeholders a shared object to review.
Why data modeling exists
Data without a model still has a shape. That shape may come from a form, an API response, a spreadsheet, or the first application code that wrote a record. An accidental shape becomes expensive when many systems depend on it.
Modeling replaces accidental choices with explicit decisions. It helps you answer questions such as:
- What does one record represent?
- Which attributes describe that record?
- How is each record identified?
- Which relationships are required or optional?
- Which values and combinations are valid?
- Which queries and updates matter most?
- How will meaning and structure change over time?
The result is not only a diagram. The result includes definitions, keys, constraints, naming choices, and implementation details that keep the design usable.
Three levels of detail
Data modeling commonly moves through three levels. Each level answers a different kind of question.
| Level | Main question | Typical contents |
|---|---|---|
| Conceptual | What matters to the domain? | Major entities, relationships, and business rules |
| Logical | How is the information organized? | Attributes, identifiers, cardinality, and normalized structures |
| Physical | How will one technology store it? | Tables or collections, columns or fields, data types, constraints, and indexes |
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://www.ibm.com/think/topics/data-modeling
Supports
- Data models provide a consistent way to define and manage data resources
- Conceptual models describe high-level entities, relationships, constraints, and business rules
- Logical models add attributes and formal relationships without technical system requirements
- Physical models specify implementable schemas, keys, and database-specific properties
- Modeling proceeds through entities, attributes, relationships, keys, normalization, validation, and iterative refinement
- Relational, entity-relationship, and dimensional models serve different design purposes
- https://learn.microsoft.com/en-us/previous-versions/troubleshoot/microsoft-365/microsoft-365-apps/access/database-normalization-description
Supports
- Normalization organizes tables and relationships to reduce redundancy and inconsistent dependencies
- Repeated facts create storage and maintenance problems when updates must agree across copies
- First normal form removes repeating groups and identifies related sets with primary keys
- Second normal form separates values that apply to multiple records and relates them with foreign keys
- Third normal form removes fields that do not depend on the record key
- Normalization can require additional tables and practical designs may balance it against performance
- https://www.postgresql.org/docs/current/ddl.html
Supports
- Relational data is stored in tables
- Physical data definition includes tables, defaults, generated columns, constraints, schemas, partitioning, and related database objects
- https://www.postgresql.org/docs/current/ddl-constraints.html
Supports
- Check constraints enforce Boolean expressions on accepted row values
- Not-null constraints require present values
- Unique constraints enforce uniqueness for a column or column combination
- Primary keys identify rows with unique and non-null values
- Foreign keys maintain referential integrity between related tables
- An associative table with foreign keys can implement a many-to-many relationship
- Referential actions define behavior when referenced rows are deleted
- https://www.mongodb.com/docs/manual/data-modeling/best-practices/#link-related-data
Supports
- Document models can link related data through embedding or references
- Embedding fits related data that is accessed together and remains bounded
- References fit independently queried, independently changing, unbounded, or complex relationships
- Schema design should follow application access patterns rather than copy a relational shape mechanically
- https://www.mongodb.com/docs/v8.2/applications/data-models-relationships/
Supports
- MongoDB models relationships by embedding related data or connecting separate documents with references
- Official examples cover one-to-one, one-to-many, many-to-many, and tree relationships
- https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
Supports
- Star schemas classify model tables as dimensions or facts
- Dimension tables describe business entities and support filtering and grouping
- Fact tables store observations or events and values for summarization
- Dimension keys and values determine fact dimensionality and granularity
- Fact tables should load data at a consistent grain
- Analytical model design can differ from normalized source structures