Data Modeling
Data modeling designs how entities, relationships and measurements are represented in stored data. It connects domain meaning to schemas, keys and constraints, helping applications and analytical systems preserve valid relationships while choosing storage structures that support their expected queries and changes.
What it is
A conceptual model identifies domain entities and relationships; a logical model defines attributes and identifiers; a physical model maps them to storage structures and indexes. These views answer different questions and should not be collapsed into one table diagram. Keys establish identity, constraints encode valid states and the grain states what one record represents. Analytical models may deliberately denormalize data for consumption, while transactional models often emphasize integrity and update behavior. The central mechanism is making assumptions about identity, cardinality and time explicit so joins and updates preserve meaning rather than merely producing syntactically valid results.
What the work involves
The practitioner starts from business events and query needs, defines record grain and agrees on identifiers and relationship cardinalities. They choose types, constraints and temporal representation, then test representative inserts, updates and joins. Useful artifacts include an entity model, field definitions and migration plans. Design reviews examine how history is retained and how missing or changing identities are handled. Physical optimization follows those semantics: an index or partition can speed a query, but cannot repair a model that double-counts events or conflates distinct entities.
Illustrative example
An order system initially stores one row per order, then adds multiple shipments. Joining shipment rows directly to order totals duplicates revenue. The modeler defines shipment grain separately and documents the relationship, while an analytical model aggregates shipments before joining order-level measures. Tests include partially shipped and canceled orders, ensuring the schema and queries reflect real lifecycle states rather than only the simplest completed order.
Limits and common mistakes
A technically valid schema can still encode the wrong domain assumptions. Excessive normalization can complicate consumption, while denormalization can create inconsistent copies or obscure update rules. Flexible schemas do not remove the need for shared semantics. Quality checks should test the meaning of joins, history and aggregation, especially where entities merge or change over time. Performance is one design constraint, not a substitute for correct identity and grain.
Prerequisites
Related skills
- → is part of: Data Engineering
Sources and further reading
- PostgreSQL: data definition
Official relational schema, key and constraint documentation; broader modeling choices are explained independently.
Last updated: 2026-10-10