Don't Panic — Data Modeling

Data modeling is the work of deciding what data means before a system starts politely filing guesses in a database. The database is very good at storing what it receives. It is considerably less gifted at noticing that three teams used the word Customer for three different things.

Start with the useful thought that a model is a set of promises. A promise says what one record represents, how it is identified, what it may relate to, and which states must be rejected. Tables, documents, and diagrams are the envelopes carrying those promises. They are not the promises themselves, which saves a surprising number of meetings from being replaced by more decorative meetings.

The first useful distinction is between a conceptual model, a view of the important domain concepts, a logical model, which adds attributes and relationship rules, and a physical model, which chooses tables or collections, types, keys, and constraints for one technology. These are viewpoints, not ceremonial paperwork. Their job is to stop a database-specific choice from masquerading as a business rule.

Two ideas do most of the heavy lifting. Identity answers how one persistent thing remains distinguishable from another, even when a tempting value such as an email address changes. Grain says exactly what one record means. If one row means one product line on one accepted order, an order total does not become a line value merely because it fits in a column. It can be copied and summed later, at which point arithmetic will cheerfully certify the wrong answer.

Relationships need their own small interrogation. How many records may participate? Must one exist? Does the association carry data? Order Line exists because quantity and sale price describe one product on one order, not Order or Product alone. That is the moment where a line in a diagram becomes a decision someone can test.

The next surprise is that normalization and denormalization are not rival teams with matching T-shirts. Normalization reduces repeated facts and the chance that their copies disagree. Denormalization deliberately groups or repeats data for a measured access need. The second choice is valid only when the query, duplicated facts, and synchronization behavior are all named. Otherwise it is an accidental second source of truth wearing a performance hat.

The rest of the course turns those promises into working checks. The Introduction explains the levels, keys, constraints, workloads, and limits. Slides condense their relationships. The Cheatsheet gives you review questions and design records. The practice exercise asks you to model an order process and prove what invalid writes your design rejects. Start there when the topic stops being a diagram and starts being a system.

Don't Panic — Data Modeling

Relevant careers