数据库字典表设计

http://junjunbest.iteye.com/blog/395421

 

================================================================================================================================

 

字典表设计及应用举例      为了响应志峰兄弟的需求，今天抽了点时间写点关于字典表设计的东西，顺便结合一个小的应用对设计做个用例体验。
咱先来看看什么叫字典。
     时间紧张，先略了，以后再谈呵呵
字典存在的必要性及他的好处。
     同上^_^
字典设计思路。
     字典信息在系统中充当基础参数的角色，基本上有些重要的基本信息是要在系统在为正式业务服务之前就由系统管理员维护进去的，有的字典信息是在使用过程中由系统管理员或者其他用户维护进去的。

     也就是说，我们有“维护字典信息”的需求，那么我们在设计字典信息表结构的时候就要考虑到这个需求。
     在维护的时候，我们肯定希望能对字典信息分文别类的进行维护，这样我们需要设计一个字典类类别表（Dic_Type），结构如下：
     ID(字典类型ID)
  Name(字典类型名称)

     具体表数据特征请看下面的例子。
     有了类别表后，我们还需要一个存放每个类别的字典信息的具体数据表(Dic_Data)：\

     ATID(自动增长的ID，没有实际作用)
  TypeID(字典信息归属的字典类别ID)
  ID(字典信息ID，在程序中使用的字典ID就是这个)
  Name(字典信息内容)

     这样，在我们的业务信息表中，存放的和字典相关的字段的值就是Dic_Data中的ID的值，那么就涉及到在界面上显示信息的问题，如果不做处理，显示出来的肯定就是原始的字典信息的ID，肯定不是用户希望得到的，基于这个需求，
  我们为每个类型的字典信息做一个视图（具体方法见后面的例子），
  将信息表与对应的视图做关联查询就可以得到字典信息ID对应的真正内容。

应用举例。
     假定做一个学生信息管理系统
     字典类型表设计如下(Dic_Type)：
     ID Name
     1  Sex
     2  ...

     字典内容表设计如下(Dic_Data)：
     ATID TypeID ID Name
     1    1      1   男
     2    1      2   女
     3    2      1   ...
     4    2      2   ...
     5    2      3   ...
     6    2      4   ...
    ...  ...    ...  ...

     性别类型字典的视图(VW_Sex):
     select ID,Name from Dic_Data where TypeID=1

     假设学生信息表如下T_Student:
     ID Name  ... Sex
     1  采采  ...  1
     2  花花  ...  2
     3  刚刚  ...  1

     取学生列表信息可通过如下方法实现：
     select T_Student.ID as StudentID,T_Student.Name as StudentName,VW_Sex.Name as SexName
     from T_Student left join VW_Sex on T_Student.Sex=VW_Sex.ID

     结果如下：
     StudentID  StudentName  SexName
     1              a            男
     2              b            女
     3              c            女

 

时间仓促，写的粗糙了点，还请见谅，有时间再补充。。。

绿色通道：好文要顶关注我收藏该文与我联系

 

================================================================================================================================

 

我们现在在进行数据库字典表设计时，有二种方式，其一是传统的方式，每个字典表都有ID、Name两字段。第二种方式是将所有字典表的数据放在同一张表中，结构如下：

TypeTable(typeID,typeName)【主表，用来记录字典表表名信息】；DataTable（typeID,DataId,DataName）【从表，记录所有字典表数据信息】

如性别、婚姻状态，在TypeTable中是两条记录，{02，性别}，{06，婚姻状态}；而在DataTable中各有三条记录{02,0,女 / 02,1,男 / 02,9,其它}，{06,0,未婚 / 06,1,已婚 / 06,9,离异}

另有一张病人列表patient（patientID,SexID,MarryStatusID…）

现在需要查询病人信息，sql语句如下：

Select b.DataName as SexName,c.DataName as MarryStatusName

from patient a left join DataTable b on b.DataId=a.SexID and b.typeID=’02’

left join DataTable c on c.DataId=a.MarryStatusId and b.typeID=’06’

在数据库中执行该Sql语句，由于DataTable约1000条左右的数据量，相对第一种方式必将对数据库有一定的影响。

（当然在实际业务中可能类似的字典表约达5－10个，patient的数据量约500w条）

但不知道在一个系统中所有字典表获取数据都采用这种方式对数据库性能到底影响到什么程度，约降低百分之几的性能?会有其它隐患没？

================================================================================================================================

楼上的可能没明白楼主的意思。

不是指学历表和国籍表数据量大，而是指人员表所具有的属性可能太多（这里不一定指人员表，也可能是其它的实体，即随着系统的复杂程度增加，实体的属性增加）。这里以人员为例,说了国籍和学历两个属性，如果人员还有职位，那么必然多出职位表，如果还有其它...

那即，当取得一条实例的完全数据时，那将进行几十个表的join,楼主考滤的应该是这个问题。

person_info(person_id,name,country_id,education_id,position_id,....)
country(country_id,name,...)
position(position_id,name,...)
education(education_id,name,....)
...

所以楼主采用了另一种设计方式：
所有属性类(属性本身也是实体,只不过是主表的某个属性)放置在一个表中，用属性名和属性值来区别。
persion_info(persion_id,name,...)
1 aaa
2 bbb
attributes(attributes_id,persion_id,attributes_name,attributes_value)
1 1 country china
2 1 education 小学
3 1 position 公司总裁
4 2 country usa
5 2 education 硕士
6 2 postion DBA