【问】如何应对关系型数据库中列的不断增加

光看文章的题目可能很难明白我想说什么，还是先描述一下项目中遇到的问题吧。 我们项目中一张这样的表用来保存各种资源，假设为资源1、资源2等等，用ResGenre来标识。 我们可以认为 资源类型 是一个抽象的概念，资源1、资源2这些都是 资源子类 。一开始所有

光看文章的题目可能很难明白我想说什么，还是先描述一下项目中遇到的问题吧。

我们项目中一张这样的表用来保存各种“资源”，假设为资源1、资源2……等等，用ResGenre来标识。

我们可以认为资源类型是一个抽象的概念，资源1、资源2这些都是资源子类。一开始所有这些子类型都只有ResId，ResName等几个字段，一张ResInfo表就可以满足需求了。但是我们都知道项目千变万化，唯一不变的就是“变化”。随着业务的发展可能资源子类型越来越多，头疼的是原来的这张表的几个字段已经满足不了需求了，各个子类型的字段在不断扩充，最头疼的是它们加的字段都各不相同……这时该怎么办？

大概有以下几种办法：

 

单表继承

所谓单表继承就是所有的字段都保存在一张表上，增加字段时就扩充原来的表。

这种方法优点简单粗暴，当子类型很少以及子类型的特殊属性很少的时候还是可取的。但是如果子类型达到了10几个，而且子类型的字段很多时缺点也显而易见:冗余太多，某一行记录存在许多与当前子类无关的属性，而且页面管理起来也相当繁琐，每次增加一个字段的时候，所有子类型都受到影响。

 

每个子类型创建一个表

添加一个子类型就增加一张表。

        ……

两个子类型存储完全独立，每增加一张表页面就要重新管理一张表，子类型很多的时候这种方式也不是很好。

 

多表继承

既有基表，又有子类型表，就像面向对象里面的继承。

多表继承的方式可以减少字段的冗余，但是同样的子类型很多时，表较多，管理起来比较麻烦。

 

半结构化数据模型

如果有很多子类型或者必须经常增加新的字段支持，那么可以用一个BLOB列来存储数据，用XML或者JSON格式。

 Property是一个属性列：它可以用Json来存储额外增加的字段:同时包含了字段名字和值。

{
     "Field1":"Value1","Field2":"Value2"
}

这种方式实际上是在关系型数据库里运用了nosql的思想，有点实现了MongoDB的无模式文档存储的意思，但是我们都知道无模式的存储好处是扩展方便，坏处是更新修改麻烦。我们用的是sql，解析json或xml起来肯定比用C#或Java麻烦，所以使用这种方式我更倾向于将更多的业务逻辑抽离应用程序的代码中处理。

 

使用NoSql

这种处于关系型和非关系型之间数据存储要求，让我们第一个想到的肯定是MongoDB。而且MongoDB sql to aggregation基本实现了常用的关系型操作。但是考虑各种其他因素，这种方法成本比较高。

上面的几种方法经过反复斟酌，不用nosql那么关系型数据库还应该干关系型数据库的事情，我放弃了一开始用“半结构化数据模型”这种方式的打算，但其他两种方式我也觉得不太合适，下面说下我最后准备使用的方法。

 

使用行转列的方式

和多表继承类似，有一个基表用来保存各个子类型共有的字段，这张表也可以叫做索引表，故名思议索引建立在上面。

ResGenre表就是用来定义资源子类型的，ColumnMeta表用来定义新增列的元数据:

 

ResGenreColumnRelation表用来绑定一个子类型有哪些字段：

最后一张表来绑定一条资源记录扩展列的值：

那么如何在查询的时候获取到一个资源的扩展字段和对应的值呢？如图一个测试表有数据如下：

可以看到子类型14,15,16绑定了不同的列。

我们想要得到结果应该是这样的：

可以看到返回的结果集其实也是冗余的，对于一个ResID没绑定的列为NULL。

在sqlserver2005中实现行转列的方式不需要再用CASE WHEN了，用PIVOT方便多了：

<span>SELECT</span> <span>*</span>
<span>FROM</span><span> ColumnDataBind 
PIVOT
(
    </span><span>Max</span>(ColValue) <span>for</span> <span>[</span><span>ColName</span><span>]</span> <span>in</span> (<span>[</span><span>Age</span><span>]</span>,<span>[</span><span>High</span><span>]</span>,<span>[</span><span>Sex</span><span>]</span>,<span>[</span><span>Weight</span><span>]</span><span>)
)TBL</span>

需要注意的是PIVOT中必须要用聚合函数。因为ResGenreColumnRelation表用ResID和ColName作为键，所以PIVOT聚合时ColValue只有一个，用Max就行了。

也可以跟上查询条件，减小操作数据集：

<span>SELECT</span> <span>*</span>
<span>FROM</span><span> ColumnDataBind 
PIVOT
(
    </span><span>Max</span>(ColValue) <span>for</span> <span>[</span><span>ColName</span><span>]</span> <span>in</span> (<span>[</span><span>Age</span><span>]</span>,<span>[</span><span>High</span><span>]</span>,<span>[</span><span>Sex</span><span>]</span>,<span>[</span><span>Weight</span><span>]</span><span>)
)TBL
</span><span>where</span> genreid <span>=</span><span>14</span>

当然既然实现的是能随便扩展列，那么一个资源绑定了哪些列肯定不知道的，动态行转列也是必不可少的，有了PIVOT也很简单：

<span>declare</span> <span>@sql</span> <span>varchar</span>(<span>8000</span><span>)
</span><span>select</span> <span>@sql</span> <span>=</span> <span>isnull</span>(<span>@sql</span> <span>+</span> <span>'</span><span>],[</span><span>'</span> , <span>''</span>) <span>+</span> ColName <span>from</span> ColumnDataBind <span>group</span> <span>by</span><span> ColName
</span><span>set</span> <span>@sql</span> <span>=</span> <span>'</span><span>[</span><span>'</span> <span>+</span> <span>@sql</span> <span>+</span> <span>'</span><span>]</span><span>'</span>
<span>print</span> <span>@sql</span>

<span>exec</span>(<span>'</span><span>SELECT * FROM ColumnDataBind PIVOT(Max(ColValue) for [ColName] in (</span><span>'</span><span>+</span><span>@sql</span><span>+</span><span>'</span><span>))TBL</span><span>'</span>)

这是在sqlserver2005+中实现动态行转列的方式，不知道MySql有没有PIVOT。

通过这种方式当增加一个列的时候通过页面就能搞定，数据库就不需要频繁的改动了，而且更符合关系型数据库的操作。

 

但是写完我又在“行转列”和“半结构化数据模型”之间犹豫了？前者应该能给予更大的灵活，但总觉得哪里很别扭，后者用起来估计也比较麻烦，设计经验不足，不知道哪位大神能指点指点？