简介这是一份面向数据库开发者、管理员及项目负责人的新闻发布系统数据库设计模板围绕数据库环境说明、命名规则、逻辑设计、物理设计等关键内容展开可直接作为新闻类站点或内容管理系统数据建模的参考规范。资源包为单个 doc 文档大小约 241KB结构紧凑、便于查阅文档先给出数据库环境与命名规则再从表汇总入手对用户信息表、新闻表、留言表、新闻类别表等核心数据表进行字段级说明包含表结构、索引、视图及存储与检索思路可帮助读者快速理解新闻发布业务的数据关系并迁移到同类系统中。目前已有 114 人学习该资源适合正在搭建新闻发布系统、撰写数据库设计文档或希望系统规范数据库设计流程的初中级开发者学习使用。1. 新闻发布系统数据库设计一份 2012 年设计文档的现代复现接手老项目最头疼的不是读代码而是把版本库里那份数据库设计文档重新变成能跑的库。新闻发布系统是典型的“四张表”业务用户、新闻、留言、类别规模不大却完整覆盖了主键设计、外键关联、文本字段选型、密码加密和权限系统集成这几类绕不开的问题。下面以一份 2012 年的《新闻发布系统数据库设计报告》为底稿把逻辑设计、物理结构和安全性方案逐个拆开讲并给出可直接执行的 T-SQL 脚本和核对查询。正在做课程设计、接手老系统维护、或者需要把历史设计文档反推成库表的人都能找到对应章节直接照着抄。2. 逻辑设计与命名规则从业务动作推导四张表拿到需求直接写 CREATE TABLE是最容易翻车的一种开始方式。新闻发布系统的数据量级和业务边界决定了模型的上限游客浏览新闻、管理员发布稿件、访客留言本质上只有三个动作。把动作映射成实体就是新闻、类别、留言和后台用户四张表。先画业务流程图再定表比对着页面输入框建字段要稳得多。2.1 为什么四张表够用这个系统里管理员是唯一的内容生产者所以用户表不需要角色字段权限判断交给 ASP.NET 的成员管理系统游客是唯一的内容消费者留言表只需要记录 IP 和内容不需要为游客建独立档案。很多初学者会把管理员和普通注册用户塞进同一张用户表导致表结构里出现一堆可空字段头像、积分、等级这些字段对新闻发布场景毫无意义。四张表的设计之所以成立是因为它严格按业务流程划分实体边界没有引入“未来可能会用”的占位字段。这个取舍看起来朴素却是区分数据库设计好坏的分水岭。2.2 dbo 前缀与表命名规则的实际含义原文档的命名规则写了两条数据库命名全部由英文小写字母组成、单词之间用下划线分割表命名为 dbo_表义名。这里有一个容易误解的点需要澄清SQL Server 里dbo是默认架构名schema不是数据库名前缀。文档里写的dbo.News完整访问路径是“数据库名.架构名.表名”也就是news.dbo.News。实际建库时数据库名是news所有表挂在dbo架构下表名直接用News、Category这类业务词即可。原文档写的dbo_表义名本质是强调用架构限定名区分同名业务表而不是让表名本身带 dbo 后缀。下表是四张表的实体划分与关联关系建表顺序依赖关系一目了然表 2.1 四张表的实体划分与关联表名实体含义核心字段与其他表的关联dbo.User后台管理员UserID, UserName, UserCode业务鉴权字段独立dbo.Category新闻分类CategoryID, CategoryName被 News 引用dbo.News新闻内容NewsID, NewsTitle, NewsContentCategoryID 引用 Categorydbo.Comment游客留言CommentID, CommentContent, CreateTimeNewsID 引用 News2.3 先建库再建表从 PowerDesigner 生成脚本到手工初始化原文档用 PowerDesigner 9.0 画 ER 图再用 SQL Server 查询分析器执行生成的脚本。PowerDesigner 的物理数据模型可以直接生成带扩展属性的 T-SQL 脚本直接执行没问题但脚本很啰嗦不容易看出建库顺序。我更愿意手动写初始化脚本结构清晰也方便团队评审-- 新闻发布系统数据库初始化不存在才创建避免重复执行报错 IF DB_ID(Nnews) IS NULL BEGIN CREATE DATABASE news ON PRIMARY ( NAME Nnews_data, FILENAME ND:\SQLData\news_data.mdf, SIZE 16MB, FILEGROWTH 16MB ) LOG ON ( NAME Nnews_log, FILENAME ND:\SQLData\news_log.ldf, SIZE 8MB, FILEGROWTH 8MB ); END GO这段脚本先通过DB_ID(Nnews)判断数据库是否已存在防止重复执行时抛错。ON PRIMARY指定主数据文件LOG ON指定日志文件。FILEGROWTH设成固定 16MB 而不是默认的百分比是为了避免数据库在快速增长阶段出现大量碎片化的小幅度扩展。实际部署时数据文件与日志文件应当放在不同物理磁盘上这里为了演示写在了同一目录。建库之后按依赖顺序建表先Category再News因为外键引用 Category最后Comment因为外键引用 News。顺序反了会出现“引用对象不存在”的报错这也是手工脚本比 PowerDesigner 一次生成更不容易出意外的地方。3. 物理表结构拆解类型选型、主外键与字段命名陷阱逻辑设计回答“有哪些实体”物理设计回答“字段用什么类型、是否可空、默认值怎么设”。文档里的四个表结构任何一张单独拿出来看都有值得展开的设计点这里按用户、新闻、留言、类别的顺序依次拆开顺带指出哪些字段设计放到今天需要修正哪些可以保留原样不动。3.1 用户表 dbo.UserCHAR(20) 存不下哈希后的密码文档里用户表以UserID为主键字段有UserName、UserCode密码、UserQQ、UserAge、UserEmail。整理后的结构如下右侧是结合当前安全要求的修正建议表 3.1 用户表结构与原设计对比字段原设计类型可空建议修正说明UserIDINT否INT IDENTITY主键自增UserNameVARCHAR(10)否NVARCHAR(20)登录名支持中文UserCodeCHAR(20)否NVARCHAR(128)密码哈希存储UserQQVARCHAR(20)是NVARCHAR(20)备用联系方式UserAgeINT是可删年龄不是刚性字段UserEmailVARCHAR(50)是NVARCHAR(100)邮箱最需要修正的是UserCode的类型。CHAR(20)是定长字符存明文密码勉强够 20 个字符但如果按文档 6.2 节的要求把密码做加密后再存储MD5 需要 32 位十六进制、SHA-1 需要 40 位、SHA-256 需要 64 位加上盐值更长CHAR(20)直接放不下。类型和加密算法必须绑定在一起考虑这是一个容易在设计阶段漏掉、开发阶段爆雷的坑。3.2 新闻表 dbo.NewsTEXT 升级为 NVARCHAR(MAX)外键要显式声明新闻表字段简单但建表脚本里藏着三个值得注意的决策。先看脚本CREATE TABLE dbo.News ( NewsID INT IDENTITY(1,1) NOT NULL, NewsTitle NVARCHAR(100) NOT NULL, NewsContent NVARCHAR(MAX) NOT NULL, CreateTime DATETIME NOT NULL CONSTRAINT DF_News_CreateTime DEFAULT(GETDATE()), CategoryID INT NOT NULL, CONSTRAINT PK_News PRIMARY KEY CLUSTERED (NewsID), CONSTRAINT FK_News_Category FOREIGN KEY (CategoryID) REFERENCES dbo.Category(CategoryID) ); GO提示原文档把标题类型写成VACHAR(100)这是VARCHAR(100)的笔误。这里直接升级为NVARCHAR(100)因为新闻标题含中文和全角符号用 Unicode 存储可以避免排序规则导致的乱码。脚本逻辑说明NewsID INT IDENTITY(1,1)从 1 开始自增避免应用层手工维护主键CONSTRAINT DF_News_CreateTime DEFAULT(GETDATE())让不传时间的 INSERT 语句自动取服务器当前时间FK_News_Category强制新闻必须属于已存在的类别这一条比在应用代码里判断“类别是否存在”更可靠——数据库层面的约束不会被上层逻辑绕过。同时NewsContent没有沿用原文档的TEXT类型。TEXT、NTEXT、IMAGE是 SQL Server 2005 之前的遗留类型2012 还能用但已标记为弃用新代码一律用VARCHAR(MAX)或NVARCHAR(MAX)代替。两者的行为差异主要在存储管理和查询计划方面MAX类型与现有字符串函数如LEN、SUBSTRING兼容性更好。3.3 留言表 dbo.CommentUserID 还是 UserIP命名语义必须一致留言表在原文档里的关键字段是CommentID、CommentContent、CreateTime、UserID和NewsID。最有争议的是UserID——字段注释写的是“用户 IP 地址”但字段名却是 UserID。这是设计文档里很典型的一类问题命名与语义不一致。游客留言没有登录唯一能追踪的身份就是来源 IP所以这个字段本质上是UserIP类型VARCHAR(15)IPv4 最大长度 15 个字符。如果坚持叫UserID后续维护的人会下意识认为它关联用户表可能错误地加入外键约束。碰到这种名不副实的字段最省事的做法是直接重命名不要带着歧义往下传。键约束方面Comment.NewsID必须引用News.NewsID。外键建立后删除已有留言的新闻时数据库会拦截这从产品逻辑上是合理的新闻报道发布后被删除其留言应当一并处理而不是出现松散的孤儿数据。3.4 类别表 dbo.CategoryCategoryName 与 Type 到底留哪个类别表结构为CategoryID主键、CategoryName类别名、Type类别类型。CategoryName和Type的语义高度重叠一个叫“新闻类别名”另一个叫“新闻类别类”如果不是原作者解释很难说清区别。如果重建这张表底稿可以这样写CREATE TABLE dbo.Category ( CategoryID INT IDENTITY(1,1) NOT NULL, CategoryName NVARCHAR(20) NOT NULL, -- 原文档里的 Type 字段与 CategoryName 语义重叠建表时不保留 CONSTRAINT PK_Category PRIMARY KEY CLUSTERED (CategoryID) ); GO我的建议是二选一如果Type仅表示分类层级比如“一级分类、二级分类”那应该拆成ParentID做树形结构而非用Type字符串字段如果Type只是另一个名字则直接删掉保留CategoryName。多一个语义模糊的字段维护时就要为“它为什么存在”付一次成本。4. 安全性设计与 ASP.NET Membership 集成从密码哈希到权限表对接安全边界可以分三层看游客能不能碰到数据库、管理员密码以什么形式落库、后台登录权限怎样复用现成的成员管理机制。原文档第 6 节讲了密码加密第 7 节讲了成员系统映射但这两块要落到真实环境还要补一层数据库访问隔离。这一章把三层串起来讲从端口监听、账号授权开始最后落到你在代码里如何组织密码校验。4.1 三层隔离端口、账号、连接串文档只规定游客不能直接操作数据库具体怎么实施是开发者的活。常见做法是数据库服务器只监听内网 IPWeb 服务器作为唯一访问源如果 Web 服务和数据库服务装在同一台机器SQL Server 只监听 127.0.0.1不对公网开放 1433 端口。为 Web 应用单独创建登录账号只授予db_datareader和db_datawriter固定数据库角色不授予db_owner更不能用sa。连接字符串里不写明文密码改用 Windows 身份验证或者加密后的配置项。文档里把 Web 服务和数据库服务放同一台机器这种情况下用服务账号的 Windows 身份验证比在连接串里写账号密码更可控。原文档提到 SQL Server 登录模式为混合身份验证、sa 密码为 123。这是开发环境的配置上线前必须改掉要么关闭 SQL 身份验证要么至少把 sa 密码替换为强密码并且禁止远程登录。数据库账号最小化授权这条做起来成本最低收益却最直接——即使连接串泄露攻击者拿到的也只是读写权限不能改表结构。4.2 密码存储从明文到加盐哈希的落地写法2012 年的系统常用 MD5 做密码摘要当时不觉得有问题但现在 MD5 和 SHA-1 都属于被攻破的哈希算法。密码字段如果还没有固定长度正好借着数据库设计稿定型的时机改成 PBKDF2public static string HashPassword(string password, byte[] salt, int iterations 10000) { // PBKDF2 迭代生成 32 字节哈希输出为 salthash 的 Base64 using (var deriveBytes new Rfc2898DeriveBytes(password, salt, iterations)) { byte[] hash deriveBytes.GetBytes(32); byte[] combined new byte[salt.Length hash.Length]; Buffer.BlockCopy(salt, 0, combined, 0, salt.Length); Buffer.BlockCopy(hash, 0, combined, salt.Length, hash.Length); return Convert.ToBase64String(combined); } }salt应该随机生成 16 字节每次注册都不一样iterations决定计算成本10000 次是 OWASP 建议的下限性能有余量再往上加。验证时把库里取出的 Base64 解出来前 16 字节作 salt剩余 32 字节作 hash用同样的迭代次数重新计算再比较。这样即使两个用户密码相同库里的值也不一样彩虹表攻击失效。4.3 业务用户表与 ASP.NET Membership 的字段映射文档第 7 节给出的是 2012 年前后 Web Forms 项目很常见的做法后台用户信息存在自定义表BBC_EMPLOYEE登录认证走 ASP.NET 自带的成员管理系统。两边的映射如下表 4.1 自定义用户表与 ASP.NET Membership 的关联键自定义表字段ASP.NET 表字段关联作用BBC_EMPLOYEE.E_NAMEaspnet_Users.UserName保存后台管理的用户编号BBC_EMPLOYEE.E_PASSWORDaspnet_Membership.Password共同保存用户的密码这样做的好处是少改业务代码坏处是用户名一旦变更业务表和aspnet_Users的关联链路就断了。文档里默认用户名不可改所以映射关系能成立。如果你现在用的是 ASP.NET Core Identity不建议照搬这套双表映射。更合理的做法是在业务表加一列AspNetUserId直接存 Identity 表的 Guid 主键从根上避免“名字改了到处跟着改”的问题。老代码维护时保留映射关系即可新项目优先考虑在业务表里关联主键而不是依赖“名字相同”这种软关联。5. 用系统视图核对建库结果三条 SQL 把设计文档验证一遍设计文档写完、建库脚本跑完不等于工作结束。真正要做的是用 SQL 把库里的表结构拉出来跟文档逐行比对。下面这三条查询是我核对老项目时固定会跑的适用的场景是手头既有设计文档又有数据库访问权限但不确定脚本执行结果和文档是否完全一致。5.1 字段级核对一次查询列出全部表和列的元数据SELECT t.name AS 表名, c.name AS 字段名, ty.name AS 数据类型, c.max_length AS 长度字节, c.is_nullable AS 可空, CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS 主键, CASE WHEN fk.parent_column_id IS NOT NULL THEN 1 ELSE 0 END AS 外键 FROM sys.tables t JOIN sys.columns c ON t.object_id c.object_id JOIN sys.types ty ON c.user_type_id ty.user_type_id LEFT JOIN sys.index_columns pk ON pk.object_id c.object_id AND pk.column_id c.column_id AND pk.index_id 1 LEFT JOIN sys.foreign_key_columns fk ON fk.parent_object_id c.object_id AND fk.parent_column_id c.column_id WHERE t.schema_id SCHEMA_ID(dbo) ORDER BY t.name, c.column_id;sys.tables、sys.columns、sys.types是 SQL Server 系统视图分别保存表、列、类型元数据。pk.index_id 1指向聚集索引如果主键没有建在聚集索引上这个条件要改成按sys.indexes.is_primary_key判断。max_length对NVARCHAR返回的是字节数除以 2 才是字符数比对文档时要记得换算。5.2 外键完整性检查缺一条就删除时报主从冲突很多项目因为导入数据顺序的问题外键没建成但没人发现。直到运营一段时间后删除一个分类被数据库拦截才暴露问题。检查脚本SELECT fk.name AS 外键名, tp.name AS 外键所在表, ref.name AS 引用表 FROM sys.foreign_keys fk JOIN sys.tables tp ON fk.parent_object_id tp.object_id JOIN sys.tables ref ON fk.referenced_object_id ref.object_id ORDER BY ref.name;正常结果应该看到至少两条FK_News_Category和FK_Comment_News。如果少了说明建表顺序错了或者脚本生成环节丢失约束。补外键用ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY REFERENCES ...注意被引用表必须存在且对应的列有索引否则加约束时会很慢。5.3 清理 TEXT/NTEXT 的残留一步到位不导数据老项目从 SQL 2000 升级上来的最容易留下TEXT、NTEXT类型。用下面的查询扫描残留SELECT t.name AS 表名, c.name AS 列名 FROM sys.columns c JOIN sys.tables t ON c.object_id t.object_id WHERE c.system_type_id IN (35, 99) -- 35 对应 TEXT99 对应 NTEXT查出来之后执行类型转换ALTER TABLE dbo.News ALTER COLUMN NewsContent NVARCHAR(MAX) NOT NULL;ALTER COLUMN能在原表上直接完成类型转换不需要导出再导入但要理解它会锁住该表的写操作应该安排在维护窗口执行。转换完成后重新跑一遍 5.1 的查询确认max_length变成 -1MAX类型的长度在元数据里显示为 -1这一步验证就闭环了。本文还有配套的精品资源点击获取