简介数据库设计是软件开发中的重要环节而概念模型、逻辑模型、物理模型三者的区别与转换是初学者最容易困惑的部分。这份doc文档面向数据库设计初学者、软件工程学生以及需要规范设计模型的技术人员系统梳理了三种模型的定义、层次关系与典型应用场景。文档对概念模型、逻辑模型、物理模型分别展开并用对象转换对比表详细说明实体、属性、关系在不同模型间的映射规则同时结合ERWIN与PowerDesigner两个主流建模工具介绍各自支持的模型与常用操作能帮助读者理清设计思路、避免建模阶段混淆。资源为单个doc文档约228KB内容以文字、E-R图形符号和对比表格为主适合直接阅读、随身查阅或打印学习目前已有1276人浏览学习。读者可通过这份笔记快速建立数据库模型的分层认知掌握三种模型的核心特征及转换方法并了解主流工具在概念、逻辑、物理模型设计中的实际用法是一份精炼的数据库设计入门参考。1. 概念模型、逻辑模型、物理模型到底在分什么三句话说清一分钟分清做数据设计这几年我见过最多的翻车不是 SQL 写错而是三个人在评审会上吵了一下午才发现嘴里说的根本不是同一个东西。业务方说“一个订单能装好几个商品”开发说“订单明细表拿订单号当外键”DBA 说“订单表按用户 ID 做分区”。这三句话其实是在描述同一件事的三个不同层面也就是概念模型、逻辑模型、物理模型的区别。概念模型回答“业务里有哪些东西、它们之间什么关系”逻辑模型回答“这些关系怎么拆成表和字段去表达”物理模型回答“表最终落在磁盘上用什么结构去存”。很多人以为画完 ER 图就算完成建模实际只做了整个流程的三分之一。这篇笔记适合刚接触数据建模的开发、要做表设计的数据分析师以及需要评审别人模型的架构师——把这套分层方法吃透后面建表、评审、改需求都能少走弯路。2. 概念模型先别碰表结构把业务里的“名词”和“关系”理清楚概念模型是整个建模流程里最容易被跳过的环节。项目排期一紧不少人直接打开建表工具开写写出来的表结构往往带着开发自己的“预先理解”而不是业务真正的样子。概念模型的作用就是强制你先把业务规则说清楚不掺任何数据库层面的东西。2.1 概念模型的关键产物实体、属性、关系的粒度怎么定概念模型的核心产出是三样东西实体、属性、关系。实体是业务世界里的“名词”比如客户、订单、商品属性是这个名词自身携带的信息比如订单的下单时间、金额、状态关系是实体之间的业务规则比如“一个客户可以下多个订单”“一个订单可以包含多种商品”。这三个概念听着简单实际画起来最容易在“粒度”上打架。常见做法是实体必须能回答“它在业务里是不是一个独立被管理的对象”。如果两个名词在生命周期、归属关系、属性集合上都一样那就该合并成一个实体如果属性差异很大即使名字相近也要拆开。要素概念模型里的形态常见错的画法实体业务名词如客户、订单、商品写成“用户表”“订单表”这种表结构语言属性实体自身的特征如金额、时间、状态把另一实体的特征挂过来导致属性归属混乱关系业务规则如一对多、多对多画成外键关联提前引入实现细节基数一个客户对应N个订单不标基数导致后续映射时全靠猜这里有个规律值得记住属性必须依附于实体存在并且只属于这个实体。比如“收货地址”如果同一个地址可能要服务多个订单那它就应该被提出来当作独立实体如果只是订单的一次性快照那它就是订单的属性。这个判断会直接影响后面逻辑模型里要不要多建一张地址表。2.2 画概念模型的通用步骤与 ER 图符号从名词清单到关系图我在实际项目里画概念模型通常按四步走。第一步圈定业务范围把所有业务名词列出来。这一步不设上限把业务方嘴里出现过的所有“东西”都扔进白板比如客户、销售员、订单、商品、仓库、物流单。第二步过滤。凡是只描述别的对象状态的词划为属性凡是能单独被查询、被统计、被管理的词保留为实体。比如“订单状态”是属性“订单”本身是实体。第三步给实体之间画关系线并标注基数。基数用 1、N、M 表示比如客户-订单是 1:N订单-商品是 N:M。注意多对多关系在概念模型阶段不需要拆中间表那是逻辑模型的事这里只要标出业务规则确实是“一个订单能装多个商品一个商品也能出现在多个订单里”。第四步把关系逐个用业务语言念一遍给业务方听。这一步是概念模型真正值钱的地方——你画了一张图然后说“一个客户可以下多个订单一个订单包含多个商品”让业务方当面确认对错。大多数人把概念模型画完就急着往表上转恰恰漏掉这个确认动作后面改起来成本翻倍。2.3 概念模型的自检三个问题判断模型是否合格画面完了怎么知道画得对不对我会用三个问题来自检。第一个问题能不能用一句话把这张图讲给业务方听而且对方能点头如果讲的时候你自己都在绕说明实体关系还没理顺。第二个问题模型里有没有出现“主键”“外键”“索引”“表”这类数据库词汇出现一个就说明你在概念层掺了实现细节要退回重画。第三个问题从模型里能不能推导出业务方关心的报表比如“想统计每个客户的累计下单金额”这个概念模型里必须能看出客户和订单是有关系的否则就是关系线画漏了。一个合格的概念模型本身就是一份和业务方沟通的通用语言。它不负责回答数据库怎么建只负责把业务规则固定成一份双方认可的“契约”。3. 逻辑模型把业务关系“翻译”成表、字段、主外键概念模型拿到手接下来这步是把“业务世界”翻译成“数据世界”。逻辑模型的核心任务只有一个把实体、属性、关系转换为不依赖具体数据库产品的表结构描述。这一步做得好不好决定了后续改需求时你是改一张表还是改十张表。3.1 从概念模型映射到关系模型实体变表关系变外键把概念模型转成逻辑模型有一套固定的映射规则我一般这样操作。规则一每个实体对应一张表实体的属性对应表的字段。实体叫“客户”表就叫可识别的名字比如 CUSTOMER属性里的“客户编号”“客户名称”“注册时间”直接变成字段。规则二1:N 关系在“多”的那一侧加外键。客户和订单是 1:N那就在订单表上加一个“客户ID”字段指向客户表的主键。规则三N:M 关系必须拆分成中间表。订单和商品是 N:M就生成一张“订单明细”表它的字段包括订单ID和商品ID再加上这个订单里商品的数量、成交价等和关系本身有关的属性。规则四属性和字段类型要定下来。概念模型里只叫“下单时间”逻辑模型里要定成 DATETIME 还是 TIMESTAMP金额要定成 DECIMAL(10,2) 而不是 FLOAT。这个决策直接影响后续计算的精度。用一张表格总结映射关系概念模型要素逻辑模型映射结果注意事项实体一张表表名用业务名词不用缩写属性一个字段先定类型和长度长度宁可大一位1:N 关系“多”侧表加外键外键字段名统一避免“ID”满天飞N:M 关系中间表中间表主键尽量用联合主键或自增ID1:1 关系合并成一张表或同一主键除非字段量差异极大否则别拆表3.2 规范化到什么程度第三范式是默认边界逻辑模型阶段绕不开“规范化”。规范化就是消除数据冗余避免同一份信息存储在多处改一遍漏一遍导致不一致。范式等级满足条件解决什么问题第一范式1NF字段值不可再分每个字段保存一个值解决“字段里塞多个值”的初级冗余第二范式2NF满足 1NF且非主键字段完全依赖主键解决“部分依赖”常见于联合主键表第三范式3NF满足 2NF且非主键字段不互相依赖解决“传递依赖”常见于字段冗余到其他表BCNF每个决定因素都是候选键处理更复杂的主键冲突日常少用实际项目里规范化的默认目标就是第三范式。拿订单场景举例一张订单表存了下单客户ID和客户名称如果客户名称是直接写进订单表的那就是典型的传递依赖——客户名称属于客户实体却依赖订单表的主键存在。后果是客户改了名字历史订单里存的还是旧名字报表数据就对不上了。第三范式要做的就是把这些字段拆回去订单表只存客户ID客户名称留在客户表里查询时通过 JOIN 取。这样客户名称只维护一份永远不会出现“同一个客户两张表里两个叫法”的问题。3.3 逻辑模型里最容易翻车的两个点冗余外键和过度拆分做逻辑模型时最常见的两个错误一个是该拆的不拆一个是拆得太碎。该拆的不拆典型例子是订单表里存了“商品名称”。这会导致商品改名时所有历史订单都要跟着改。正确的做法是订单明细表里只存商品ID和下单时的快照价如果业务要求显示“当时的商品名”再考虑单独加一个商品名称快照字段并明确它只属于订单业务不属于商品表。另一个极端是拆得太碎。有人把订单状态“待支付、已支付、已发货”拆成三张表每张表记录状态变更历史。结果查一个订单当前是什么状态要 JOIN 三张表再取最大时间查询性能一塌糊涂。状态这种“当前值”直接用字段存状态变更历史才需要单独的表。判断标准很简单这个数据是需要查“当下”还是查“曾经”需要查当下就存字段需要追溯才建表。逻辑模型的评判标准我认为只有一条在不做任何物理层优化的前提下用这套表结构能不能把概念模型里的业务规则完整表达出来并且满足新需求的扩展空间。能就说明这一层合格了。4. 物理模型把逻辑模型落到磁盘、内存和索引上逻辑模型定的是“表长什么样”物理模型定的是“表在数据库里真怎么跑”。同一个逻辑模型物理设计做得好不好查询性能可以差几十倍。这一层是 DBA 和资深开发的主战场也是刚入行的人最容易觉得“玄学”的部分。4.1 物理模型多了哪些东西存储结构、索引、分区、约束逻辑模型里的一张表到物理模型阶段至少要补四类信息。第一类是存储参数包括字符集、排序规则、表空间。字符集定错中文查不出来或排序不对后面改要重建表。常见做法是跟库统一用 utf8mb4MySQL或对应数据库的 Unicode 字符集排序规则则根据是否需要区分大小写选。第二类是主键策略。自增主键写入快但迁移麻烦UUID/雪花 ID 分散写入但占用空间大。对于订单类大表业界常做法是雪花 ID对于字典表、配置表自增主键完全够用。第三类是索引设计。索引不是越多越好每多一个索引就多一份写入开销。物理模型阶段要明确哪几条查询路径是核心路径针对核心路径建复合索引并且要注意字段顺序。第四类是分区和归档策略。表里的数据是无限增长的订单表三个月后可能就是几千万行。不提前做分区查询会全表扫描备份恢复也慢。用表格列一下物理模型阶段需要决策的参数决策项常见取值判断依据字符集utf8mb4有中文、表情符号统一用这个主键类型BIGINT 自增 / 雪花ID写入并发高选雪花迁移简单选自增索引按查询路径建复合索引别给低频查询建索引分区按时间按月分区订单、日志类有明确时间维度的表压缩冷数据表开压缩归档表读多写少压缩可省空间4.2 一张表的物理参数应该怎么定常用参数与改法从逻辑模型换到物理模型时我通常的做法是先按默认参数把表建出来再根据三个问题调整参数。第一个问题是这张表的核心查询是什么第二个问题是数据多久涨一倍第三个问题是这张表是否接受写入延迟。先看查询。如果核心查询是“按客户ID查近三个月订单”那分区键选 order_time复合索引建 (customer_id, order_time)。注意索引字段的顺序很关键等值条件放前面范围条件放后面否则索引效果直接减半。再看增长。如果每个月新增 500 万条数据那按年分区就不合适按月分区才能避免一个分区过大。分区也不是越多越好分区数过多查询优化器在分区裁剪上的开销反而变大。最后看写入。如果这是一个流水账表每秒高并发写入那就不要建太多索引更不要建在余额这类高频更新的字段上否则每次更新都要同步改索引写入性能直线下降。下面给一个订单表的物理模型设计示例配合索引策略表名t_order分区按 order_time 做 RANGE 分区每月一个分区主键order_id 使用雪花 ID索引idx_customer_time (customer_id, order_time)覆盖“查某个客户的历史订单”场景字段类型amount 用 DECIMAL(12,2)不用 FLOATstatus 用 TINYINT 加注释不用 VARCHAR这套设计最大的好处是查询走索引范围限定在单月分区内数据增长时只需要新增分区不影响现有查询。4.3 从逻辑模型到物理模型的重构反规范化该不该做物理模型阶段还有一个绕不开的动作就是反规范化。逻辑模型为了消除冗余把数据拆得很清但物理模型要考虑查询性能有时候不得不把拆开的字段重新合并回来。什么场景需要反规范化最典型的是报表大宽表。正常范式设计下统计“客户每月下单金额、订单数、客单价”要 JOIN 三四张表跑一次全量报表要几分钟。反规范化的做法是建一张汇总表把客户ID、月份、订单总数、订单总金额直接预计算好存进去查询从“实时 JOIN 计算”变成“查一张汇总表”。但反规范化是有代价的数据会被重复存储业务端更新时要同步维护多份数据稍不留神就会出现数据不一致。所以我一般只在两类场景做一类是高频读、低频写的统计汇总场景另一类是查询路径特别复杂、JOIN 超过三张表的场景。其余情况老老实实走规范化不要为了微优化把复杂度引进来。物理模型的意义就在于它把逻辑模型里“正确的表结构”翻译成“能扛住真实流量的表结构”这一步没做好后面上线就是一场灾难。5. 概念、逻辑、物理三者的边界与常见避坑五个真实翻车点这部分我踩过的坑比较多挑了五个最有代表性的写下来。每一条都是“现象→原因→解决”的结构排查问题的时候可以对照着看。5.1 现象把概念模型的关系直接当物理表来实现我见过一个团队拿到概念模型后看到“客户和订单是 1:N”就直接建了两张表看到“订单和商品是 N:M”就建了第三张表。表面看没错但问题在于他们把概念模型里的基数直接套到物理表上没有经过逻辑层的判断。原因跨过了逻辑模型跳步实现。概念模型里的关系只是业务规则它不回答“订单明细表要不要单独设主键”“商品ID要不要冗余进订单表”这些具体设计问题。解决建表之前先做一张映射表写下每个概念实体对应哪张逻辑表、每个业务关系对应哪种约束。这张映射表就是逻辑模型的简易版把它填完再动物理设计。5.2 现象逻辑模型里提前加入了索引和主键策略评审时看到有人递过来的逻辑模型里写着“order_id 用雪花ID索引加在 customer_id 上”。这其实是把物理模型的内容混进了逻辑模型。原因分不清三个层级的边界的表现。主键策略、索引设计都属于物理层跟具体的数据库选型强相关。逻辑模型应该在完全不确定数据库产品的情况下也能成立。解决画图时强制分隔逻辑模型里只写“字段名、字段类型、主外键标识、约束”任何带存储含义的词一律划到物理模型文档里。养成这个习惯后换数据库的时候你就知道哪些要重做、哪些不用动。5.3 现象物理模型设计完发现一年后数据量超预期查询开始变慢一个订单表建的时候没有分区跑了八个月后突破千万行每到月初统计翻倍慢。原因物理模型阶段没有预估数据增长。逻辑模型阶段是看不出数据量级的物理设计时必须引入“预计数据量×增长周期”的估算。解决建模时就要问清楚业务方这张表一年大概涨多少行然后按“两倍于预期”做容量规划。订单类表直接默认做分区设计即使前期用不上也不亏。5.4 现象命名不一致表名字段名各叫各的同一个字段业务文档里叫“customer_id”代码里叫“userId”建表脚本里变成“客户编号”。后期维护的时候每次都要翻文档做一次翻译。原因没在建模阶段定命名规范每个人按自己的习惯来。解决建模时就定下统一的命名词典。表名用业务名词全称字段名统一小写加下划线不混用驼峰同一个含义的字段在相关表里保持同名。这个规范一旦定下来后面不管多少人经手模型的可维护性都能立住。5.5 现象概念模型、逻辑模型、物理模型各画各的改了一处别的没跟上概念模型里加了一个字段逻辑模型加了但物理模型的建表脚本没更新上线时数据直接查不到。原因三个模型分散在不同文档里没有建立一致性的校验手段。解决把这套建模过程纳入版本管理模型变更必须同步产出三层变更记录。更省事的做法是让逻辑模型成为唯一事实源用建模工具直接从逻辑模型生成物理模型脚本。人工同步环节越少脱节风险越低。6. 三个模型都画完怎么验证没画错自检三步法模型画完不是终点上线前能做三轮快速验证每一轮都能提前暴露问题。第一轮逐字段做映射矩阵。把概念模型的每个实体属性列出来对应到逻辑模型的字段再对应到物理模型的列定义。三步对应不上说明哪里断了。这一步我一般用表格做竖着是实体名横着填三层映射结果。一次走完就知道遗漏了多少东西。第二轮走一遍核心 CRUD 路径。挑出系统里最关键的三个业务动作比如下单、支付、查历史订单把对应的 SQL 写出来跑一遍 EXPLAIN确认索引有没有真正命中。如果 SQL 要 JOIN 的表超过三张回到物理模型想想是不是该做冗余或汇总表。第三轮做数据容量预估。拿业务方给的增长量算一下一年后的行数在测试库里灌上这个量级的数据跑一遍核心查询看响应时间。这一步能验证分区方案和索引设计是不是真的扛得住而不是只在小数据量下跑得动。最后说一个我自己的教训。以前做后台系统时我从概念模型直接跳到建表脚本逻辑模型没细想结果业务需求变更时订单表要加“退款状态”连带改了三张表还出了一次数据不一致的事故。后来老老实实按三层建模走才发现真正花时间的是把业务规则确认清楚而不是写建表脚本。希望帮到你。本文还有配套的精品资源点击获取