简介数据库设计中的概念模型、逻辑模型与物理模型是容易被混淆的三个核心概念。这份文档面向数据库初学者、软件设计师及需要系统梳理数据建模方法的开发人员分别讲解三种模型的定位概念模型用实体-关系图描述业务语义逻辑模型细化数据结构和关系物理模型则落到表、字段、主外键、索引等存储实现。文档还给出了对象转换对照表将实体、属性、关系如何转化为表、字段和外键讲得比较清楚并分节介绍ERWIN、PowerDesigner对概念模型、逻辑模型和物理模型的支持差异与常用操作便于读者从业务需求一路推导到数据库表结构。资源共1个doc文件压缩包228KB可直接用Office或WPS打开阅读。目前已有1275人学习浏览内容结构完整既适合作为数据库课程的复习笔记也可在项目设计阶段充当快速参考。1. 三个模型的边界数据库设计评审的第一个分歧点做订单模块时数据库评审会上最常见的分歧是有人拿 E-R 图叫概念模型有人管它叫逻辑模型还有人直接对着表结构谈字段长度。这种分歧不只是叫法问题它会把需求阶段的业务讨论拉进存储细节也会让设计工具用错层在概念层画了物理外键或者在物理层还在争论实体语义。ERWIN 只提供逻辑模型和物理模型两个视图PowerDesigner 15 提供概念、逻辑、物理三模型这种差异恰恰说明三层之间不是绝对固定的分层而是对抽象粒度的划分不同。下面先拆清三模型的边界与转换规则再分别用两个工具走一遍从实体到建表语句的完整流程。按这条路径走一遍评审时提到的“第几层”就不再需要解释。2. 概念模型、逻辑模型、物理模型的分层规则与转换2.1 概念模型的问题域抽象概念模型是对真实世界中问题域内的事物的描述不是对软件实现的描述。E-R 图是它最常用的表达形式矩形表示实体椭圆表示属性菱形表示关系关系有三种基数一对一、一对多、多对多。在概念模型的建模过程中实体的子类别是容易被忽略的一块。E-R 图中的子类实体用 IS-A 关系连接超类例如教师既是教职工的子类也是独立的实体PowerDesigner 里用 Inheritance 表达ERWIN 里用 Complete/Incomplete Sub-category 表达。子类的引入主要解决属性继承和不完整分类的问题这在概念层就决定了逻辑模型的粒度。概念层还有一些容易出错的习惯给实体添加主键、给关系标注外键实现、把多对多直接翻译成中间表。这些都属于逻辑模型或物理模型的关注点。概念模型就该停在“业务里有哪些实体、实体之间什么关系”连实体属性都可以是描述性的不需要定义完整字段列表。2.2 逻辑模型的细化与主键落定逻辑数据模型反映的是系统分析设计人员对数据存储的观点是对概念模型进一步的分解和细化。这一步要做的事情包括为每个实体补全属性为属性定义唯一性约束确定主键把多对多关系转换为关系实体。例如订单与产品是多对多关系。概念模型里它们之间可以是一条菱形关系在逻辑模型里就必须拆成“订单产品明细”实体用两个外键关联到订单和产品否则关系无法在关系型数据库中落地。这是逻辑模型阶段最典型的分解操作。需要注意的是逻辑模型仍不依赖具体数据库产品。字段确认到“名称、是否为空、主键与否”即可不需要确定是 VARCHAR(32) 还是 CHAR(32)。类似的默认值、字符集、存储引擎这类物理属性在逻辑模型中不出现。如果设计中过早陷入这些参数往往会把讨论焦点从数据结构拉偏到性能调优。2.3 物理模型的 DBMS 绑定物理模型是对真实数据库的描述设计对象是表、视图、字段、数据类型、长度、主键、外键、索引、是否可为空、默认值以及存储过程、触发器等。它也允许一定程度的反范式设计比如为查询性能在订单表中冗余客户名称字段这在逻辑模型阶段是不合适的但在物理模型中可以按访问场景做权衡。物理模型中的关系通过外键约束或中间表实现一对一关系可以把外键放在任意一方一对多关系在“多”方表上添加外键列多对多关系则依赖关系表。外键的删除规则、是否建索引、约束命名方式都是物理模型需要明确的决策点。2.4 三模型对象转换对照以下表格是三个模型之间的对象转换关系评审时最常拿来逐行核对设计对象概念模型逻辑模型物理模型实体与属性实体、实体属性实体完整属性表、字段关系一对多/多对一菱形关系关系/候选外键外键约束关系多对多菱形关系关系实体中间表 两个外键主键无需确定需确定主键主键约束字段细节无需完整定义无需确定长度和类型类型、长度、默认值、可空性从概念模型到物理模型每一步转换都会增加细节也会引入错误风险。概念层的子类在物理层可能变成单表加类型字段也可能变成多表继承逻辑层的“n 对 n 关系实体”在物理层有可能被建模为复合主键表或自增主键表。这些转换没有绝对等价需要按业务写入场景确认。2.5 一个例子从概念到物理的完整推演以一个员工分配部门的场景演示三步走。概念模型实体“员工”有工号、姓名、入职日期属性“部门”有编号、名称属性员工与部门之间的分配关系是“多个员工属于一个部门”即多对一。逻辑模型为员工补全部门编号属性作为外键员工主键设为工号部门主键设为编号关系在逻辑上表现为员工表指向部门表的外键列。物理模型绑定时选择 MySQL 5.7建表语句可以简化为下面的样子CREATE TABLE department ( dept_id INT NOT NULL AUTO_INCREMENT COMMENT 部门主键, dept_name VARCHAR(50) NOT NULL COMMENT 部门名称, PRIMARY KEY (dept_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT部门表; CREATE TABLE employee ( id INT NOT NULL AUTO_INCREMENT COMMENT 员工ID, emp_no VARCHAR(20) NOT NULL COMMENT 工号, emp_name VARCHAR(50) NOT NULL COMMENT 姓名, hire_date DATE NOT NULL COMMENT 入职日期, dept_id INT NULL COMMENT 所属部门ID, PRIMARY KEY (id), UNIQUE KEY uk_emp_no (emp_no), KEY idx_employee_dept (dept_id), CONSTRAINT fk_employee_dept FOREIGN KEY (dept_id) REFERENCES department (dept_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT员工表;逻辑说明employee 表的 dept_id 允许为 NULL表示员工存在“未分配部门”的中间状态如果业务上不允许未知部门则可以改为 NOT NULL。emp_no 上加唯一约束是逻辑模型阶段确定的唯一性规则idx_employee_dept 是为外键列建的普通索引MySQL 一般不自动为外键列创建索引这属于物理模型必须补的细节。这里的主键选择了自增 id 而不是 emp_no是为了避免业务工号变动导致主键更新波及外键引用同时保留 emp_no 的唯一约束作为候选键这正是逻辑模型里“标识符”落到物理层的一种常规取舍。3. ERWIN 的逻辑物理双模型设计与 SQL 导出3.1 ERWIN 为什么只做两层模型ERWIN 提供 Logical Model 和 Physical Model 两种模型类型并且另有一种 Logical/Physical Model那不是第三种模型而是同时把逻辑视图和物理视图放在同一张模型图里展示在实体层级显示逻辑名称在字段层级显示物理名称和注释。这种设计思路基于一个现实概念模型在很多项目里就是由实体关系草图替代的真正落到数据库时逻辑与物理之间的差异主要在于字段类型和约束细节。ERWIN 干脆把这两层放在一起操作减少来回切换。使用 Logical/Physical 视图时字段注释功能只有在该模式下才能显示因为注释本质是物理模型字段属性的延伸。3.2 ERWIN 中的关系类型与删除策略ERWIN 的逻辑模型里提供 Identifying relationship、Non-identifying relationship 和 Many-to-many relationship 三种关系。物理模型对应的是 Independent table、View table以及 Identifying relationship 和 Non-identifying relationship。Identifying relationship 与 Non-identifying relationship 的差异来自外键是否作为子表主键的一部分关系类型外键是否进入子表主键删除父表行为使用场景Identifying relationship是子表存在关联数据时父表删除失败并报错子表依赖父表存在如订单明细Non-identifying relationship否子表外键字段置为空子表可独立存在如员工与部门在 ERWIN 物理模型中删除父表数据时如果子表有关联数据且是 Identifying 关系则删除失败且报错冲突如果是 Non-identifying 关系则把子表对应外键字段值置为 NULL。这个行为与外键 ON DELETE RESTRICT 和 ON DELETE SET NULL 的语义一致。生成 DDL 之前要特别确认子表外键字段允许为空否则置空规则会导致插入失败。3.3 从实体到 SQL 的操作步骤以员工-部门为例在 ERWIN 中操作路径如下新建模型时选择 Logical/Physical 模型目标 DBMS 选 MySQL 5.0 以上版本。在 Logical 视图下创建实体 Employee、Department双击实体打开定义窗口在 Columns 页填写字段General 卡片中勾选 Primary Key 复选框将字段设为主键。字段注释是在 Column 的属性页里填写的生成物理模型时注释会自动落到 COMMENT 上。用工具栏的 Non-identifying relationship 工具在两个实体之间画关系关系线从 Department 一端指向 Employee 一端表示部门一方的键进入员工表作为外键。物理视图下核对生成的外键字段名称、是否为空以及索引选项。执行 Menu - Database - Choose database 切换目标数据库。每个 DBMS 定义的数据类型映射不同从 Logical 转 Physical 时字段类型会根据目标库自动映射。导出 SQL 使用 Menu - Forward Engineer/Schema Generation。Preview 预览完整 DDLReport 可以将 SQL 写出到文件。导出后的 MySQL 建表脚本与手写的差异通常集中在约束名和索引CREATE TABLE Department ( DeptID INT NOT NULL AUTO_INCREMENT COMMENT 部门ID, DeptName VARCHAR(50) NOT NULL COMMENT 部门名称, PRIMARY KEY (DeptID) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT部门表; CREATE TABLE Employee ( EmpID INT NOT NULL AUTO_INCREMENT COMMENT 员工ID, EmpNo VARCHAR(20) NOT NULL COMMENT 工号, EmpName VARCHAR(50) NOT NULL COMMENT 员工姓名, HireDate DATE NULL COMMENT 入职日期, DeptID INT NULL COMMENT 所属部门ID, PRIMARY KEY (EmpID), CONSTRAINT fk_Employee_Department FOREIGN KEY (DeptID) REFERENCES Department (DeptID) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT员工表;逻辑说明这版 SQL 对外键列 DeptID 没有自动建索引。ERWIN 默认生成的脚本里不保证为外键列建索引如果删除或更新父表主键子表外键列在做约束检查时会全表扫描行数上去后锁竞争明显。我通常会在生成 SQL 后给每个外键列补一条普通索引。约束命名最好带表名与字段名ERWIN 默认名在库表过多时很难一眼定位问题。注意切换目标数据库时字段类型映射结果不一定符合预期。例如同样是字符串MySQL 5.0 与 PostgreSQL 的映射长度单位不同生成后要逐列确认 TEXT、VARCHAR 的长度和默认值。3.4 显示注释与模型同步ERWIN 里显示字段注释不是打开一个开关就能全局生效的前提是模型类型必须为 Logical/Physical。在工具栏将显示模式切换到 Physical实体以表格形式展示物理字段此时表字段下方可以看到已填写的注释切到 Logical 显示的是实体属性和逻辑名称。如果拿到一个只有 Physical 模型的 ERWIN 文件列注释不会凭空出现需要从数据库导入字段信息或在工具中反向填充。常见做法是先在数据库端用 ALTER TABLE 补好 COMMENT再用逆向工程重新生成模型。逻辑模型名称与物理表名不一致时生成 SQL 会以 Physical 的名称为准所以不要只在 Logical 层改名就认为表名已更新。4. PowerDesigner 15 三层模型的逐级落库4.1 CDM 概念模型与 LDM 逻辑模型的边界PowerDesigner 12 里只有 Conceptual Data Model 和 Physical Data Model 两类PowerDesigner 15 开始补齐了 Logical Data Model形成完整的三个模型层级。CDM 里的概念实体用 Entity 表示关系用 Relationship 表示另外提供 Association 表示“关联”要素它和 Relationship 的主要区别是 Association 可以挂属性Relationship 不能挂属性。例如“订单与产品的多对多关联”如果需要记录“下单数量”在 CDM 中应选择 Association 而不是 Relationship。CDM 中的 Inheritance 表达实体继承比如教职工、教师、行政人员的关系Association Link 用于连接 Entity 和 Association并设置 0-1、0-n、1-1、1-n 等基数。这里的基数决定了后续外键的可空性。LDM 在 CDM 的基础上将属性定义完整为每个实体确定主键和唯一性约束并保留多对多关系标记为 n-n Relationship。LDM 中的 Entity 与 CDM 近似但语义更接近表结构已经具备转化为物理表的基本条件。4.2 PDM 物理模型的表与引用PDM 中元素包括 Table、View、Reference、Procedure、Link/Extended Dependency。View 对应数据库视图Reference 对应外键关联Procedure 对应存储过程。Table 的属性包括表名、列名、数据类型、主键、外键、索引等。Reference 在 PDM 中分为强制性和非强制性两种强制 Reference 要求子表外键列必须存在对应父表记录非强制 Reference 允许外键为 NULL。删除父记录时的行为由外键类型与 DBMS 映射共同决定这部分和 ERWIN 中 Identifying/Non-identifying 关系的行为对应得上。4.3 从 CDM 到 PDM 的模型生成步骤PowerDesigner 的三层迁移有两种路径从 CDM 直接生成 PDM或先 CDM 再 LDM 再 PDM。我一般建议经过 LDM因为逻辑模型是业务规则落库前的最后一道核对点在这里确认主键、候选键和多对多分解后再进入 PDM可以避免物理层反复调整。操作路径如下新建模型时选择 Conceptual Data Model。在图形区创建 Entity名称用中文表达Code 用英文小写双击实体补充属性Attribute 的 Name 和 Code 同样中英文分离。画出两个 Entity 之间的多对多关系使用 Association 或 Relationship若关联本身带属性用 Association。选择 Tools - Generate Logical Data Model生成 LDM。这一步会要求选择生成选项例如是否包含 Inheritance 和 Domain。在 LDM 中核对主键、唯一约束、n-n 关系如果存在多对多PDM 生成时会默认创建中间表。选择 Tools - Generate Physical Data Model选择目标 DBMS。切换或修改数据库用 Database - Change Current DBMS例如从 MySQL 5.0 切到 PostgreSQL 9.5。生成 SQL 使用 Database - Generate Database选择脚本输出文件。如果只需要看某张表的 DDL双击表打开属性窗口切到 Preview 选项卡即可不需要全库导出。以一个典型多对多场景为例订单表 Order 和产品表 Product 之间通过 OrderItem 建立关系。PDM 生成 SQL 大致如下CREATE TABLE Order ( order_id SERIAL PRIMARY KEY, customer_name VARCHAR(50) NOT NULL, order_date TIMESTAMP NOT NULL ); CREATE TABLE Product ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price NUMERIC(10,2) NOT NULL ); CREATE TABLE OrderItem ( order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, PRIMARY KEY (order_id, product_id), CONSTRAINT fk_orderitem_order FOREIGN KEY (order_id) REFERENCES Order (order_id), CONSTRAINT fk_orderitem_product FOREIGN KEY (product_id) REFERENCES Product (product_id) );逻辑说明OrderItem 的主键选择 (order_id, product_id) 是逻辑模型中多对多转换后保留的复合主键。如果业务上允许同一订单多次录入同一产品就要改为自增主键否则会出现主键冲突。quantity 字段在 CDM 中挂在 Association 上LDM 转换后落到 OrderItem 表这是三层转换最典型的属性迁移路径。这里 Order 是保留字生成 SQL 时用双引号包裹MySQL 下对应的是反引号切换 DBMS 后引号风格会自动变化。4.4 Name 与 Code 的命名分离与切换PowerDesigner 的 Name 和 Code 是两套命名Name 显示中文业务语义Code 是数据库对象名。从 CDM 生成 PDM 时默认把 Code 作为物理表名和列名。工具选项中 Tools - Model Options - Naming Convention 可以设置是否把 Name 同步到 Code或者指定大小写转换规则。依赖这套机制同一套逻辑模型可以切换不同 DBMS 生成对应物理模型错误做法是直接用中文 Code 生成表名导致后续 SQL 脚本和程序映射都要做额外处理。推荐在建模第一块实体时就定下命名规范Code 统一小写、单词间用下划线分隔约束名带表名和字段名如 fk_user_role_user_id索引名统一 idx_表名_列名。这样生成 SQL 后无需手工调整。5. 三模型评审时的验证清单与工具生成 SQL 的核对点三模型评审最容易犯的错是只看图不看对象数量。把概念模型的实体数、逻辑模型的实体数、物理模型的表数列成清单对比数量对不上的一定是转换时漏了要素概念模型里画的 n 对 n 关系逻辑模型没有对应关系实体物理模型就不会有中间表逻辑模型确定的主键物理模型里变成联合主键后唯一约束是否有变化也要逐表核对。5.1 外键索引与删除规则核对PowerDesigner 和 ERWIN 生成 SQL 时都不会默认给外键列补索引。用一个 information_schema 查询可以找出外键列上缺少索引的表SELECT DISTINCT rc.constraint_name AS fk_name, kcu.table_name AS child_table, kcu.column_name AS fk_column, kcu.referenced_table_name AS parent_table FROM information_schema.REFERENTIAL_CONSTRAINTS rc JOIN information_schema.KEY_COLUMN_USAGE kcu ON rc.CONSTRAINT_NAME kcu.CONSTRAINT_NAME AND rc.CONSTRAINT_SCHEMA kcu.CONSTRAINT_SCHEMA LEFT JOIN information_schema.STATISTICS s ON s.table_schema kcu.table_schema AND s.table_name kcu.table_name AND s.column_name kcu.column_name WHERE rc.CONSTRAINT_SCHEMA your_database AND s.index_name IS NULL;逻辑说明这个查询先把 REFERENTIAL_CONSTRAINTS 与 KEY_COLUMN_USAGE 连接得到外键列再用 STATISTICS 左连接判断该列是否已经存在于某个索引中s.index_name IS NULL 即该列没有被任何索引覆盖。这里判断的是外键列是否为索引的第一个字段如果外键列只是组合索引的第二列这个查询仍然会漏报需要结合执行计划逐条确认。删除规则也要单独检查工具默认生成的外键约束经常不显式写 ON DELETE 子句删除父表记录时数据库默认使用 RESTRICT 语义SELECT CONSTRAINT_NAME, DELETE_RULE, UPDATE_RULE FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA your_database;DELETE_RULE 出现 CASCADE 时要确认这是业务允许的级联删除不是工具默认生成的规则。这里最容易出事故的是 ERWIN 中 Non-identifying relationship 转 ON DELETE SET NULL 后子表外键列一旦被误设为 NOT NULL删除父记录时会直接报错评审时要把外键列可空性与删除规则放在一起看。5.2 注释落库与从库到模型的闭环工具导出的 SQL 中字段注释经常不生成。检查脚本如下SELECT table_name, table_comment FROM information_schema.tables WHERE table_schema your_database AND table_comment ;查到空注释的表补一句 ALTER TABLE ... COMMENT...把建模时写在 Logic 层的说明同步回数据库。老系统改库时用 Database - Reverse Engineer 读入现网表结构对照逻辑模型检查差异把手工补的索引、默认值和约束命名更新回物理模型否则下次重新生成 SQL 时会被覆盖丢掉。按这份清单把外键索引、删除规则、注释三块过一遍再回头改的就不是模型图而是具体 DDL 了。本文还有配套的精品资源点击获取