简介数据库设计中的概念模型、逻辑模型与物理模型是层次分明又容易混淆的核心概念这份文档对此进行了系统梳理适合数据库初学者、软件专业学生及需要规范建模的开发人员阅读。内容从模型种类入手分别介绍概念模型E-R图、逻辑模型和物理模型的含义并通过对象转换表对比实体、属性、关系在各阶段的映射差异同时结合ERWIN、PowerDesigner等常用工具说明三种模型的实际应用方式。文档还列出了模型区别要点及常见操作有助于读者快速理解从业务需求到数据库表结构的完整设计路径。资源为1个doc文档压缩包大小228KB目录结构完整包含模型种类、常用工具、常用操作等章节便于按需查阅。目前已有1275人学习下载适合作为数据库设计入门与复习的参考笔记。1. 概念模型、逻辑模型、物理模型差一层改动成本差十倍一个需求在开发后期才确认“一个用户要同时绑定多个门店”。如果这个需求在概念模型阶段被提出来画 E-R 图的人改两条关系线、确认一组基数就行到逻辑模型阶段要动主键、外键可能还要新增中间表到物理模型阶段就是 ALTER TABLE、刷存量数据、重建索引。概念模型、逻辑模型、物理模型是对同一个业务现实的三次抽象一个回答“业务里有什么”一个回答“关系怎么组织”一个回答“数据怎么存”。搞清三者区别是数据建模、数据库设计、数仓分层设计的基本功。下面从 E-R 图开始一层层落到 DDL最后用 information_schema 做反向核对每个步骤都能直接照做。2. 概念模型用 E-R 图把业务说清楚别碰字段类型2.1 概念模型只回答“业务里有什么”概念模型是三层里抽象程度最高的一层服务对象是业务方和产品经理最常见工具是 E-R 图。核心元素只有三个实体、属性、关系。实体是业务里客观存在的东西比如用户、门店、订单属性是实体的特征比如注册时间、订单状态关系描述实体之间的关联并且必须标注基数一对一、一对多、多对多。基数不是画着好看的它直接决定后面逻辑模型里外键往哪张表放、要不要拆中间表。画概念模型最常见的错误是顺手把主键、字段类型、索引写进去。一旦写进去这张图就不再是业务视图业务方看不懂评审会开成数据库设计评审。概念模型阶段出现在纸上的应当是“一个用户可以下多个订单”而不是“customer_id 是 BIGINT 主键”。基数和业务规则在这一层确认最便宜到后面每改一次成本都上一个量级。2.2 一个订单业务的概念模型示例以连锁门店订单系统为例先只画业务结构不要想表怎么建。下面这张表是评审时最容易被业务方看懂的格式实体与属性用业务语言列全关系单独标注在最后一列业务方不需要懂任何数据库概念就能读。实体核心属性业务语言关系用户账号、昵称、注册时间1 个用户对应 N 个订单门店门店名称、所在城市、营业状态1 个门店对应 N 个订单订单订单号、下单时间、支付状态、总金额1 个订单包含 N 个商品商品商品名、售价、分类N 个商品可出现在 N 个订单中需要和业务方逐条确认的是基数用户和订单的一对多门店和订单的一对多订单和商品的多对多。多对多尤其不能放过比如“一个商品参与多个促销活动、一个促销活动包含多个商品”如果业务方表达得含糊就追问到有明确答案为止否则逻辑模型阶段没有依据去拆中间表后面所有关联查询都要返工。2.3 概念模型的完成标准业务方能签字概念模型做到什么程度算完成常见判断标准有三条。第一把业务全流程走一遍确认实体没有遗漏比如做订单系统时漏了“退款单”后面补实体的成本远高于一开始画出来。第二每条关系基数都被业务方确认过不能由开发靠业务猜测。第三命名统一用业务术语不用拼音缩写也不叫“a 表 b 表”。评审的签字人应当是业务负责人而不是开发负责人开发在这个阶段的职责是提问把“一个用户同时是两个门店的会员怎么办”这类边界抛给业务方而不是急着定义字段。3. 逻辑模型实体转表、属性转列、关系转键3.1 四条映射规则与多对多的处理逻辑模型把概念模型翻译成与具体数据库无关的关系结构翻译规则业界已经很成熟实体变成表属性变成列一对多关系在“多”的一端加外键多对多关系拆成中间表。一对一关系可以合并进一张表也可以拆成两张表用相同主键关联取决于两侧属性的访问频率和更新频率差别大就拆否则合并更简单。前面概念模型里订单和商品是多对多这里必须拆出订单商品中间表否则只能在订单表里放一个逗号分隔的商品 ID 列表直接违反第一范式后续 join 和统计全部难做。中间表主键一般取双方外键的组合同时带上数量、单价这类只在两者关联时才有意义的属性。这个拆表动作是概念模型到逻辑模型最重要的转换点很多设计在这里偷懒后面再补一张中间表意味着所有历史查询重写。3.2 用标准 SQL 把逻辑模型定下来逻辑模型不绑定具体数据库产品但通常用标准 SQL 把结构定下来作为交付物。下面这段 SQL 是订单子域的逻辑模型注意里面只出现表、列、键和约束没有任何引擎、字符集、索引字样。-- 逻辑模型订单子域标准 SQL不绑定具体产品 CREATE TABLE customer ( account_no VARCHAR(20) PRIMARY KEY, -- 业务账号直接做主键 nickname VARCHAR(50), registered_at DATE ); CREATE TABLE store ( store_id INT PRIMARY KEY, -- 门店号 store_name VARCHAR(50) NOT NULL ); CREATE TABLE product ( product_id INT PRIMARY KEY, product_name VARCHAR(100) NOT NULL ); CREATE TABLE orders ( order_id INT PRIMARY KEY, -- 订单号 account_no VARCHAR(20) NOT NULL REFERENCES customer (account_no), store_id INT NOT NULL REFERENCES store (store_id), order_status VARCHAR(10) NOT NULL, -- 状态流转枚举值由应用层约束 created_at TIMESTAMP NOT NULL ); -- 多对多落地订单与商品的中间表 CREATE TABLE order_item ( order_id INT NOT NULL REFERENCES orders (order_id), product_id INT NOT NULL REFERENCES product (product_id), quantity INT NOT NULL, PRIMARY KEY (order_id, product_id) );这段代码里有三个值得展开的决策点。第一customer 用 account_no 做业务主键还是引入自增 ID是逻辑模型阶段就要拍板的事业务账号做主键查询直观但账号规则一旦变化比如允许注销后账号重用历史关联会出错生产上更常见的是系统生成的整数主键加唯一约束兜底。第二order_item 的复合主键 (order_id, product_id) 在这里先定下来物理建模时这个复合键会直接决定索引形态。第三外键关系用 REFERENCES 在逻辑层声明清楚物理层用不用约束可以再议但逻辑层必须体现关系完整性。提示逻辑模型里用 REFERENCES 声明外键不代表物理层必须建外键约束只代表关系在逻辑上成立物理层是否启用约束由并发写入性能决定。3.3 字段字典与范式检查逻辑模型的交付物除了建表 SQL通常还有一份字段字典逐列说明业务含义、类型、是否为空、默认值。检查字段字典主要看三件事同义词是否统一“账号”不能上一张表叫 account_no、下一张表叫 user_account类型是否正确金额必须用定点数而不是浮点数是否满足第三范式也就是非主属性不得依赖其他非主属性。列名类型空 / 默认业务含义order_statusVARCHAR(10)NOT NULL订单状态CREATED / PAID / DONEcreated_atTIMESTAMPNOT NULL下单时刻统计口径依据quantityINTNOT NULL默认 1单品购买数量最小值为 1范式不达标最常见的表现是冗余列比如在订单表里冗余“用户昵称”昵称一改历史订单全部要跟着刷。行业内的通行做法是做到第三范式就收手BCNF 以上在绝大多数业务系统里性价比不高强行拆分会把高频查询变成四五张表的 join。4. 物理模型引擎、字符集、索引与分区写进 DDL4.1 物理模型比逻辑模型多出哪些维度物理模型必须绑定具体数据库多出来的维度包括存储引擎、字符集与排序规则、索引类型、分区方案、表空间、块大小。同样一段逻辑模型在 MySQL 里要决定 ENGINE 用 InnoDB 还是别的存储引擎在 Oracle 里要写成 VARCHAR2 并用 NUMBER 表示数字在 PostgreSQL 里索引类型又不一样。所以物理模型必须一库一版无法像逻辑模型那样通用。InnoDB 和 MyISAM 的差别不必多说事务与外键支持就足以把绝大多数业务场景推向 InnoDB。字符集层面 utf8mb4 是当前事实标准因为覆盖了完整 Unicode 和 emoji。真正容易漏的是排序规则utf8mb4_0900_ai_ci 和 utf8mb4_bin 会影响相等比较与唯一约束的行为账号、单号这类需要精确区分的字段建议选 *_bin中文名称字段用 *_ai_ci 更符合搜索直觉。4.2 订单表的物理模型 DDL 示例以 MySQL 8.0 为例把 orders 落到物理层同时把 4.1 里提到的引擎、字符集、组合索引、外键取舍全部落进一段 DDL方便直接对照参数。物理模型的要点不是“能建出来”而是让索引和类型贴合真实的查询路径。CREATE TABLE orders ( order_id BIGINT NOT NULL COMMENT 订单ID, account_no VARCHAR(20) NOT NULL COMMENT 账号, store_id BIGINT NOT NULL COMMENT 门店ID, order_status VARCHAR(10) NOT NULL DEFAULT CREATED COMMENT 状态: CREATED/PAID/DONE, created_at DATETIME NOT NULL COMMENT 创建时间, PRIMARY KEY (order_id), KEY idx_store_time (store_id, created_at), KEY idx_account_time (account_no, created_at), CONSTRAINT fk_store FOREIGN KEY (store_id) REFERENCES store (store_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT订单主表;参数说明逐条过order_id 从逻辑模型的 INT 放大到 BIGINT是按订单量估算后防溢出的常规操作组合索引 idx_store_time 把门店 ID 和下单时间放一起覆盖“查某门店某时段订单”这类高频查询idx_account_time 服务“查某用户最近订单”的列表页。外键约束写在 DDL 里InnoDB 会自动为 store_id 建索引但外键对批量导入性能有拖累数据量大的场景常只保留普通索引完整性交给应用层兜底——这个取舍要写进物理模型设计说明不能只在会上口头讲。4.3 物理模型阶段的三个必调细节物理模型有三个设计点需要专门调而不是让建表工具生成默认值。第一个是字符集与排序规则。utf8mb4 基本是唯一选择排序规则则要按字段区分订单号、账号建议 *_bin中文名称字段用 *_ai_ci 更自然。第二个是组合索引的列顺序。最左前缀让 (store_id, created_at) 和 (created_at, store_id) 是两种能力前者支撑门店维度查询后者只能支撑时间范围查询设计时必须拿真实 SQL 去比对。第三个是大表的分区策略。订单表按 created_at 做 RANGE 分区按月拆分是常见做法历史数据归档直接交换分区比 DELETE 大事务高效得多。设计项逻辑模型阶段物理模型阶段日期字段created_at TIMESTAMP按月 RANGE 分区索引只声明唯一约束与外键按最左前缀设计组合索引字符集不涉及utf8mb4按字段选 collation键类型INTBIGINT防溢出5. 用 information_schema 做三层映射核对5.1 反向列出物理模型清单物理表建好后三层模型最常见的裂缝是命名不一致和约束丢失。我习惯用一条 information_schema 查询把物理模型拖出来再和逻辑模型的字段字典逐行比对比逐张表翻建表语句快得多。SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY FROM information_schema.COLUMNS WHERE TABLE_SCHEMA order_db ORDER BY TABLE_NAME, ORDINAL_POSITION;重点检查三列COLUMN_KEY 为 PRI 的主键是否与逻辑模型一致UNI 是否覆盖了逻辑模型的所有唯一约束IS_NULLABLE 为 YES 而逻辑模型标着 NOT NULL 的列逐条确认原因。外键需要另查 KEY_COLUMN_USAGE 表和逻辑模型的 REFERENCES 声明对齐外键丢失在数据量大的库很常见但不能因为常见就放过去。5.2 对不齐时的三个高发断点对账时最容易暴露三个断点。第一物理模型里多了逻辑模型没有的冗余列比如 orders 表被人加了一列 user_nickname这是违反第三范式的信号要当场确认是不是为了省一次 join 而有意为之。第二唯一约束被实现成普通索引重复数据理论上可以入库直到业务上撞出重复单号才被发现。第三类型漂移逻辑模型里的 DECIMAL(10,2) 在物理层变成 FLOAT金额精度在月度对账时对不上。这三类问题用上面那条 SQL 全库扫一遍基本都能暴露比逐表翻建表语句快得多。5.3 三层联动的改动顺序新增一个字段时常见做法是三层联动先改概念模型的实体属性再改逻辑模型的字段字典与建表 SQL最后执行物理 DDL。顺序不能倒因为概念层一动逻辑层的关系和键可能跟着变逻辑层一动物理层的索引和分区要重新评估。执行 ALTER TABLE 加列之后把 5.1 的查询重跑一遍确认新列的命名、类型、空值约束与逻辑模型一致。哪怕只是加一列备注也要让三层文档在同一时刻只存在一个版本这样下次对账时数据库里只有一个可解释的“事实”。本文还有配套的精品资源点击获取