不定字段数目的数据库表设计和数据结构

两难的境界：不定字段数目的数据库表设计和数据结构

昨天项目组会议上讨论的关于不定字段数目的数据库表问题并没有结果，今天继续分析之后发现问题可能还更大。当时讨论的结果是可能采用四种技术：

1. 动态增加数据库表字段
2. 预留足够的空白字段，运行时作动态影射
3. 用xml格式保存在单字段里
4. 改列为行，用另外一个表存放定制字段

现在我们来分析一下四种技术的优劣，不过首先可以排除的是第一点动态增加字段的方法，因为在实际操作时候几乎是不可能的（sqlserver太慢，oracle索性不支持），基本可以不讨论就排除。剩下后三点。

先来讨论预留空白字段的方法，基本原理就是在数据库表设计的时候加入一些多余的字段，看下面的代码：

CREATE   TABLE  Sample(
  name  varchar ( 12 ),
  field0  varchar ( 1 ),
  field1  varchar ( 1 ),
  fieldN  varchar ( 1 )
}

然后看实际运行时候的需要，动态分配字段给系统使用，也许需要一个这样的结构来描述分配情况：

public   class  Available
{
  public int CurrentUnusedFieldNumber;
  public Hashtable FieldToRealName;
}

也许某一时刻的数据状况是这样的： CurrentUnusedFieldNumber=3, 哈西表FieldToRealName包含内容是("field0"="SomeId", "field1"="AnyName", "field2=IsOk")

现在的问题是如果要配合Hibernate，如何来处理？以上段的数据使用状况为例子，如果我们的类定义是这样：

public   class  Entity01
{
  public string Name;
  public string SomeId;
  public string AnyName;
  public bool IsOk;
}

也许只需要修改一下xxx.hbm.xml，把 SomeId 和 field0 做成对应就ok了。但是在运行时我们怎么知道会有这样的类定义？除非我们做动态代码生成，自动编译也许可以，但是问题也许就到其他方面去了；如果我们不用动态定义，那么类就只能是这样：

public   class  Entity01
{
  public string Name;
  public Hashtable ExtraFieldAndValues;
}

使用的时候，用 entity01.ExtraFieldAndValues.setValue("AnyName", "boss") 的方式来引用，也许这样是修改最少的了，但是问题是Hibernate不支持这样的方法。

再来讨论单字段存储的方法，我们使用这样的数据库表定义

CREATE   TABLE  Sample
(
  Name  varchar ( 12 ),
  Xml CLOB( 102400 )  //  仅作说明而已
)

然后对应这样的类定义

public   class  Entity01
{
  public string Name;
  public string Xml;
  public Hashtable ExtraNameAndValueFromXml;
}

我们的代码就可以这样使用：string id = entity01.ExtraNameAndValueFromXml.getValue("SomeId") 了。这样解决看起来很不错，不仅不需要Available表，而且看起来Hibernate对它的支持也很完美，但是致命的问题在于：如果保持高效的查询？除非数据库系统本身对此有支持，否则就只能用低效的substring或者like做查询，这在大批量数据中根本就不可行。

是不是折衷一下，把两种方法的优点和起来？问题有来了：怎么保持两者之间数据的同步？难道要我们用存储过程去解析xml内容？

所以，一个两难的问题，需要我们认真去解决。我们通过认真的需求分析，也许可以减少可变字段的数量，但是只要有一个可变字段或者可变的可能性存在，我们始终要去解决这个两难的问题。

期待继续讨论。

（新增部分）

还有一种方法就是改列为行，用另外一个表存放扩展字段，定义可以如下：

CREATE   TABLE  SampleFields
(
  idSample  Integer ,
  fieldName  varchar ( 30 ),
  fieldValue  varchar ( 100 )
)

其中idSample关联到Sample表的id字段（我没有写出来）。这样的话，Hibernate很容易支持，也可以支持Sql的查询，而且可以支持把内容放到Hashtable中去，看起来是目前最好的方式了。但是在大容量数据的时候，SampleFields表的数据会是主表数据量的N倍（看定制的字段数目多少而定），同样存在有很严重的性能问题。

哪位高人还能再给一个方案？

---------2005-7-22新增-----------
很多朋友给出了很好的建议，其中蛙蛙池塘 给出了一个表结构，因为看起来不甚方便，我把类图画出来如下：

图所表示的内容简单来说是这样：
1。一个很宽的表ProductAttributeValues，包含用到的几乎所有可能的类型的值，但是每次只能用一个类型的值
2。将可变的列转为行，存放到上表中
3。为了存放类型定义和一些下拉列表的内容，设计了ProductAttributeTemplates和关联的其他表
4。ProductListItems中存放Product中所有项的说明和顺序。

这种思路其实就是把产品的“知识级”（设计模式用语）和“操作级”都表现出来了，如果要划分，则图的左上角三个表属于“操作级”，其余的属于“知识级”。wljcan 网友建议我去看《设计模式》的“观察模式”，我发现上图其实就是一种和“观察模式”相似的设计。《设计模式》看了很久都没能看下去，不过这几天正在看“观察模式”，等有心得了，再来看看能不能对上图的结构修改一下。

怡红公子 提醒说oracle和sql server对xml字段其实已经不错了，所以找了一下，但是真的是既产品中恐怕还是不敢用，不知道性能如何。虽然采用xml方式是我最推崇的方法。而且一旦采用xml方式存储，恐怕就会有changyu 网友提醒的数据类型问题，要存一个图片的话恐怕就歇菜了（当然也不是说xml中就一定不能存图片）。

不过我现在觉得xml字段还是要的，作为辅助存储手段，因为毕竟未来查询起来可能会更方便些，然后结合“观察模式”也就是类似上图的方法作为主要的存储手段，虽然有一些冗余，结合我的使用 NVelocity 解析 PowerDesigner 的cdm文件 ，工作量也不会太大。

再研究研究看看。

下面是一个不定字段数目的数据结构的解决办法是用了一个模板表

CREATE   TABLE   [ dbo ] . [ ProductAttributeDataTypes ]  (
  [ DataTypeId ]   [ int ]   NOT   NULL  ,
  [ Description ]   [ varchar ]  ( 30 ) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT   NULL  
) 

CREATE   TABLE   [ dbo ] . [ ProductAttributeListItems ]  (
  [ LookupListId ]   [ int ]   NOT   NULL  ,
  [ ListItemId ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
  [ DisplayName ]   [ varchar ]  ( 50 ) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT   NULL  
) 
CREATE   TABLE   [ dbo ] . [ ProductAttributeLookupLists ]  (
  [ LookupListId ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
  [ Name ]   [ varchar ]  ( 80 ) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT   NULL  
) 
CREATE   TABLE   [ dbo ] . [ ProductAttributeTemplateCategories ]  (
  [ PATCategoryId ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
  [ Name ]   [ varchar ]  ( 50 ) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT   NULL  ,
  [ DisplayName ]   [ varchar ]  ( 255 ) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT   NULL  
)
CREATE   TABLE   [ dbo ] . [ ProductAttributeTemplates ]  (
  [ AttributeTemplateId ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
  [ Name ]   [ varchar ]  ( 50 ) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT   NULL  ,
  [ DataType ]   [ int ]   NOT   NULL  ,
  [ LookupListId ]   [ int ]   NULL  ,
  [ PATCategoryId ]   [ int ]   NOT   NULL  ,
  [ CustomerHelp ]   [ varchar ]  ( 4000 ) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL  ,
  [ DisplayName ]   [ varchar ]  ( 255 ) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL  
) 
CREATE   TABLE   [ dbo ] . [ ProductAttributeValues ]  (
  [ ProductId ]   [ int ]   NOT   NULL  ,
  [ AttributeTemplateId ]   [ int ]   NOT   NULL  ,
  [ valueInt ]   [ int ]   NULL  ,
  [ valueStr ]   [ varchar ]  ( 255 ) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL  ,
  [ valueMemo ]   [ text ]  COLLATE SQL_Latin1_General_CP1_CI_AS  NULL  ,
  [ valueBool ]   [ bit ]   NULL  
)
CREATE   TABLE   [ dbo ] . [ ProductListItems ]  (
  [ ProductListId ]   [ int ]   NOT   NULL  ,
  [ ProductId ]   [ int ]   NOT   NULL  ,
  [ ItemDescription ]   [ varchar ]  ( 80 ) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL  ,
  [ DisplaySequence ]   [ int ]   NOT   NULL  
)

不知道大家有没有看懂这些表之间的关系,这是一个产品目录的表,产品有不确定的属性,每个属性对应的数据类型也不一样,而且有的是用下拉列表选择的,有的是 是或否,有的是一段儿文字,这里用的就是数据模板来做的,这是<asp.net电子商务高级编程>里的一部分数据库