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

A conceptual model keeps the discussion close to the domain. A retail model might contain Customer, Order, Product, and the relationships among them.

A logical model adds detail without committing every choice to one database product. It defines attributes, identifiers, and relationship rules.

A physical model maps that design to a specific system. It chooses names, types, primary keys, foreign keys, constraints, indexes, and storage-specific patterns.

These levels are viewpoints, not mandatory documents. A small project may capture them in one evolving model. The separation still helps you notice when a technical choice is being mistaken for a business rule.

The basic building blocks

An entity is a type of thing, event, or concept that the system needs to represent. Customer and Product are things. Shipment is an event. Subscription Plan is a concept.

An attribute describes an entity. A Product may have a product identifier, name, price, and status. Give each attribute one clear meaning and an appropriate domain of allowed values.

A relationship states how entities associate. A Customer places Orders. An Order contains Products. The relationship needs more detail than its verb:

• cardinality describes how many instances may participate;
• optionality describes whether participation is required;
• a relationship that has its own attributes may need an associative entity.

For example, an Order and a Product form a many-to-many relationship. An Order Line resolves it. The Order Line can also hold quantity and sale price because those values describe one product on one order.

Identity and keys

Every persistent entity needs a stable way to distinguish one instance from another.

A natural key comes from the domain, such as a country code or an assigned account number. A surrogate key is introduced by the system and has no domain meaning. Neither choice is automatically correct.

Ask whether a candidate key is unique, present, stable, and safe to expose. Email addresses often look unique but can change. A generated customer identifier is stable, while a unique constraint on normalized email can still enforce the current business rule.

In a relational database, a primary key identifies each row. PostgreSQL defines a primary key as unique and not null. A foreign key requires values to match a referenced row and maintains referential integrity between related tables.

Keys identify and connect records. They do not replace business definitions. Two teams can share the same customer identifier while disagreeing about which people count as active customers.

Constraints make rules executable

A diagram documents intent. A database constraint can reject invalid data at the storage boundary.

Common relational constraints include:

• NOT NULL when a value must be present;
• UNIQUE when a value or column combination must not repeat;
• PRIMARY KEY for the chosen row identifier;
• FOREIGN KEY for referential integrity;
• CHECK for a Boolean rule such as a positive price.

Use the database for rules it can enforce reliably. Keep cross-record, time-dependent, or context-dependent rules in an appropriate service or validation process when a database constraint cannot express them safely.

Do not turn every current observation into a permanent constraint. Confirm whether the rule belongs to the domain or only describes today's sample data.

Normalization and controlled duplication

Normalization organizes relational data to reduce repetition and inconsistent dependencies. Repeating the same customer address in Customers, Orders, Invoices, and Collections creates several places that must agree after a change.

The first three normal forms provide a practical progression:

1. First normal form removes repeating groups and identifies rows.
2. Second normal form removes dependencies on only part of a composite key.
3. Third normal form removes attributes that depend on non-key attributes.

Normalization improves update consistency, but it can add tables and joins. Denormalization deliberately stores some data together or repeats it to serve a measured access pattern. Treat denormalization as a documented tradeoff. State the query it helps, the duplicated facts it creates, and the process that keeps those copies consistent.

Match the model to the workload

A relational transaction model often favors normalized entities and enforced relationships. It supports frequent updates and protects shared facts from inconsistent duplication.

A document model can embed related data or store references between documents. MongoDB recommends choosing between those patterns from access behavior. Embed data that is usually read together and remains bounded. Use references when related data grows independently, changes independently, or needs separate access.

An analytical model serves filtering, grouping, and summarization. A star schema separates tables into dimensions and facts. Dimensions describe business entities for filtering and grouping. Facts record events or observations at a declared grain and contain values to summarize.

One design does not have to serve every workload. An operational model can preserve transaction integrity while a separate analytical model reshapes the same domain for reporting.

Grain prevents quiet errors

Grain states exactly what one record represents. Write it as a sentence before choosing attributes:

One row represents one product line on one accepted order.

Every attribute and measure must make sense at that grain. An order total belongs to the order grain. Copying it onto every order line and then summing it can overcount revenue. The query may run without error because the defect is semantic, not syntactic.

For facts, also state how time works. Decide whether a row records an event, a periodic snapshot, or the current state. This choice determines which historical questions the model can answer.

A practical modeling workflow

1. Define the decisions, operations, and questions the model must support.
2. Collect domain terms, examples, rules, and edge cases from stakeholders and source systems.
3. Identify entities or events and define each one in plain language.
4. Declare identity and grain before listing every attribute.
5. Add relationships, cardinality, optionality, and lifecycle rules.
6. Place attributes according to their dependencies.
7. Choose a relational, document, dimensional, or other structure from the workload.
8. Add enforceable types and constraints.
9. Test the model with representative reads, writes, changes, and failures.
10. Review it with domain experts and revise it as requirements change.

Use examples that challenge the happy path. Ask what happens when a customer merges with another record, a product changes category, an order is canceled, or late data arrives. Edge cases reveal hidden identity and time assumptions.

Validation and evolution

Validate a model from several directions:

• Meaning: Do stakeholders agree with each definition?
• Integrity: Can required rules be enforced?
• Workload: Do common reads and writes fit the structure?
• History: Can the model answer required point-in-time questions?
• Change: Can names, types, and relationships evolve without uncontrolled breakage?
• Security: Does the structure expose or duplicate sensitive data unnecessarily?

A model is a living design. IBM's modeling process ends with validation and returns to refinement as business needs change. Record important decisions and assumptions so future maintainers know which choices are intentional.

Limits and poor fits

A model cannot repair unclear business ownership. It can expose two definitions of Customer, but stakeholders must decide where each applies.

A model also cannot guarantee accurate input. Constraints can reject an impossible status value, but they cannot prove that a recorded delivery happened in the physical world.

More detail is not always better. A diagram with every field may hide the central relationships. A highly normalized schema may burden a read-heavy use case. A denormalized document may make independent updates unsafe. Choose the least complex design that preserves required meaning, integrity, and access behavior.

A practical learning path

1. Practice entities, attributes, relationships, cardinality, and keys.
2. Model one small domain at conceptual, logical, and physical levels.
3. Learn functional dependency and the first three normal forms.
4. Implement keys and constraints in a relational database.
5. Declare grain and design a small star schema for analysis.
6. Compare embedding and references in a document database.
7. Test models against realistic reads, writes, history, and edge cases.
8. Study migrations, temporal modeling, distributed data, and model governance.