Data Modeling is the process of deciding how business data should be
- structured,
- organized,
- related,
- represented
so that people and computer systems can use it correctly.
In simple terms: Data Modeling = deciding what data we have, what each piece of data means, how different data is related, and how we should organize it.
Data Modeling include:
- Conceptual Modeling
- Logical Modeling
- Physical Modeling
- Operational Data Modeling
- Relational Modeling
- Data Vault
- Dimensional Modeling
e.g. Kimball dimensional modeling - Other Modeling Approaches
What is Data Modeling actually DOING?
We take the real business world and translate it into a structured data representation that a computer can store, query, analyze, and maintain.
Data Modeling is about MEANING, not just tables.
The basic building blocks of Data Modeling
| English | 中文 | |
|---|---|---|
| Entity | 实体: 我们要管理的“东西” 可识别 + 业务相关 + 可以被描述 | An Entity is a distinct business object or concept that we want to represent in data. Identifiable + Relevant + Describable |
| Attribute | 属性: 描述这个东西的信息 | An Attribute describes a property or characteristic of an entity. |
| Relationship | 关系: 东西和东西之间怎么关联 | |
| Cardinality | 基数 / 数量关系 一个对多少个 | Cardinality specifies how many instances of one Entity can be associated with an instance of another Entity. 1:1, 1:N, N:1, N:N |
| Key | 键 | Cardinality specifies how many instances of one Entity can be associated with an instance of another Entity. simple, “Which exact record is this?” A Primary Key is the chosen attribute or combination of attributes that uniquely identifies each row in a table. A Foreign Key is an attribute in one entity/table that references the key of another entity/table. |
| Constraint | 约束 |
The three levels of Data Modeling
Conceptual Model
Conceptual Model is a high-level representation of the major business entities and their relationships.
Simply, what business things exist? 业务上有什么东西？
Logical Model
The Logical Data Model defines the detailed structure of the data, including entities, attributes, relationships, keys, and business rules, without depending on a specific database technology.
Simply, how should the business data be logically organized?
Physical Model
The Physical Data Model describes how the logical model will actually be implemented in a specific database, data warehouse, lakehouse, or other technology.
Simply, how will we actually store and implement the data?
| Model | Question | 中文 |
|---|---|---|
| Conceptual | What? | 有什么业务东西？ |
| Logical | How is it organized? | 数据逻辑上怎么组织？ |
| Physical | How is it implemented? | 技术上怎么真正实现？ |
What is Normalization?
Normalization is a data modeling technique used to organize data so that:
- unnecessary duplication is reduced
- data dependencies are correctly represented
- update anomalies are minimized
- data integrity is improved
Why do we normalize data?
Normalization organizes data around business entities and dependencies. It reduces unnecessary redundancy and prevents update, insert, and delete anomalies while improving data integrity and maintainability.
Normalization 是根据业务实体和数据依赖关系来组织数据，减少不必要的数据冗余，避免 Update、Insert 和 Delete Anomalies，同时提高数据完整性和可维护性。
First Normal Form — 1NF
A table is in 1NF when each attribute contains atomic values, meaning each cell represents a single value rather than a repeating group or list.
每个 Cell 应该保存一个 Atomic Value（原子值），而不是一个列表或重复组。
Second Normal Form — 2NF
A table is in 2NF when:
- It is already in 1NF 首先必须满足 1NF
- Every non-key attribute depends on the whole primary key, not only part of a composite key. 所有非 Key 属性必须依赖于整个 Primary Key, 不能只依赖 Composite Key 的一部分
Third Normal Form — 3NF
A table is in 3NF when:
- It is in 2NF
- Non-key attributes depend on the key, the whole key, and nothing but the key.
Grain
粒度
The grain defines exactly what one row in a fact table represents.
Grain 定义 Fact Table 中“一行数据到底代表什么业务事件”。
Key Takeaways
- Data Modeling translates the real business world into structured data.
是把现实业务世界转换成结构化的数据。 - An Entity represents a business object or concept.
表示一个业务对象或业务概念。 - An Attribute describes an Entity.
描述 Entity 的特征 - A Relationship describes how Entities are connected.
描述不同 Entity 之间如何关联。 - Cardinality describes how many instances of one Entity can relate to another.
描述两个 Entity 之间“一对一、一对多、多对多”的数量关系。