关系型数据库设计时id反思

关系型数据库设计反思之id

上万级数据与id的爱恨情仇

首先说一下为什么想写这个东西呢，主要是因为最近要迁移数据库，其中多张表有2万多条数据，其中一些字段还是text类型的数据，就是保存爬虫爬过来的数据，其中包含很多无效的HTML标签，包括其中的样式等等信息，导致数据量非常大， 关于怎么迁移这种类型的数据，我会重新写一篇博客，现在我想讲的一个问题就是针对这些大重量级的数据库设计时的一些反思，希望你们看了之后不会跟我一样，踩同样的坑，同时，也算是提醒自己，千万别再做这种傻逼的事情了。

先看数据结构

项目表：project

id	int	项目id
name	varchar(30)	项目名称

项目相关信息：project_news

id	int	主键
project_id	int	项目表外键
news	text	关于该项目的信息

然后按照一般人的习惯，查一个项目的信息时，最简单的实现方法是就是：

select * from project_news where project_id=10;

当然，你会给project_id添加上索引，加快查询速度，这在一般情况下是没问题的，甚至是符合数据库设计规范的，但是现在我面临的问题是：

我不止有一个数据库，我有很多数据库，每个数据库中都有这样两张表project和project_news，现在我要把他们迁移到一个数据库中，还要对其中重复的信息去做去重处理。

设想一下，每个数据库中的project_news都有2万多条数据，每条数据中又有一个很重的news字段，这样的情况下，如果你用PHP脚本来跑的话，很快就会报内存用尽的错误，或者你的代码将运行非常长的时间，最最重要的是，你需要对数据进行处理：

• 首先你需要把各个库中的project表合并，并在其中记录老的id和新的id对应关系
• 接着你需要把各个库中的project_news表中的project_id转换成新的id
• 最后你还要去掉原先带着的id字段，否则会在插入数据时出现有相同的id的情况

简单来说，上面你必须用脚本实现，那么有没有简单一点的实现方式呢？我想直接把数据导出来，不用做任何处理后直接扔进数据库中呢？

有的，但是这个需要打破我们非常定式化的一个思维：

一张表里面一定要有id字段吗？

id反思

从我们开始接触数据库开始，最常见的一个字段就是id了，自增，主键等等信息，一张表里没有id就仿佛不是一张数据库的表了一样，但是恰恰是这个玩意，会让我们陷入一些麻烦中。

就比如上面这个问题，其实我们潜意识中的一个思维定式就是：

id代表了一条数据的唯一性

比如我们有个用户表：user，我们将其与别的表关联时，在别的表中采用的外键很有可能就是user_id，也就是user表的id，但是你有没有想过一个问题，现实情况中，我们区分一个人最简单的办法就是他的身份证号码啊，所以我们完全可以设计这样的形式啊：

用户表：user

card_no	varchar(30)	身份证号码
name	varchar(30)	名字

用户账户表：user_account

id	int	主键
card_no	varchar(30)	用户身份证号码
bank_name	varchar(20)	银行名字
account	varchar(20)	对应用户的银行账号

这里我没有像一般习惯一样为user表设计一个id字段，因为我不需要采用id来存储一个用户与其账户之间的关系，下次我要迁移数据时，只需要将user_account表的数据直接导出来，撑死了不导id的数据，而且我不需要对user_account中的card_no进行处理，到了新的数据库中，他还是老的数据，一个人的身份证号码是不变的，怎么样，这样是不是简单一点了。

回到我最开始讲的项目的例子，如果我这样设计数据库：

项目表：project

project_no	varchar(20)	项目编号
name	varchar(20)	项目名称

项目信息表：project_news

title	varchar(60)	消息标题
news	text	信息详情
project_no	varchar(20)	项目编号

这里我顺道取消掉了project_news表的id字段，采用title来取代，为什么呢？因为每则消息的标题重复的可能性是微乎其微的，而且每则消息的标题也足以说明该则消息的唯一性了。下次我迁移数据时，就只要把数据一个劲的往里扔，再配合title的唯一性，筛选掉重复的数据，这样就是最简单的解决思路了。

到这里我想说明什么呢？

如果你的表需要关联别的表，下次使用id作为外键前，想想是否可以采用现实中的具体信息来作为该条记录的主键字段，毕竟id只是一个解决思路而已。

这里我举一些例子，便于你去理解：

• 商品表：商品编码
• 基金：基金编号
• 股票：股票编号
• 人员：身份证号码

因为你可能会遇到跟我不同的问题，但是遵循这个建议总是好的，因为比起你数据库中简单且只在你的关系型数据库中生效的id关联关系，我这个采用现实信息作为纽带的数据结构更加稳固。

重复部署相似环境的一些建议

看似和我上面的讲解的没什么关系，但是还是有点联系的，这里顺道提一下，以前也讲过这句话：

在一个系统上唯一的数据，如果是多个环境，那么也应该是唯一的。

这里再重复说明一下，假设我们的软件是部署在多台服务器上的，每台服务器的代码都是一样的，数据结构甚至都是一样的，不同的就是操作人员插入的数据的话，那么你一定要注意，一些编码类的数据，在每台服务器上一定要是不一样的，比如上面project，如果你的项目是内部项目，没有既定的project_no，那么你现在要自己去生成这个编号，同时你这个系统不是一家在用，而是多家在用，也就是每家都会生成他们的project_no，那么这个时候你一定要注意，每家生成的project_no都要不一样，比如你可能采用计数累加的形式：P001，P002，那么在两家会同时生成一个P001的项目，这样会一个问题，那就是当你想整合数据时，会显得根本不可能，因为你是以project_no作为项目唯一的标志，现在有两个P001，这个时候你处理起来就会很麻烦，甚至迁移数据都会很麻烦，所以一定要保证多个相同环境中生成的数据也是唯一的，第一家是P001，第二家就是S001等等，这样才会在迁移数据，统计数据时带来很多优势。