Introduction to Data Modeling

Data modeling is the practice of deciding how data is structured: what entities exist, how they relate to each other, and how that structure is represented in tables, columns, and keys. The same underlying business facts — customers, orders, products — can be modeled in wildly different ways, and the choice affects everything downstream: query performance, how easy the data is to understand, and how much duplication or risk of inconsistency you introduce.

Why modeling is a distinct skill

Writing SQL against a well-modeled schema is easy — the tables map cleanly to business concepts, joins are obvious, and metrics are unambiguous. Writing SQL against a poorly modeled schema is painful even for experts: you end up guessing which of five 'status' columns is authoritative, or writing the same complicated join in every query. Good modeling front-loads that thinking once, in the schema, instead of repeating it in every downstream query.

The questions modeling answers

• What are the core entities (things) in this domain, and what uniquely identifies each one?
• How do entities relate — one-to-many, many-to-many — and how is that relationship represented?
• What's a fact that happened (an order, a page view) versus a descriptive attribute (a customer's name, a product's category)?
• How much redundancy is acceptable in exchange for simpler or faster queries?

This section builds from those basic distinctions up through the two dominant schools of practical modeling: normalized (transactional) design, and dimensional (analytical) design used in most data warehouses.