浅谈数据库设计技巧(下)

摘自：http://www.knowsky.com/4938.html　
三、多用户及其权限管理的设计
　　开发数据库管理类的软件，不可能不考虑多用户和用户权限设置的问题。尽管目前市面上的大、中型的后台数据库系统软件都提供了多用户，以及细至某个数据库内某张表的权限设置的功能，我个人建议：一套成熟的数据库管理软件，还是应该自行设计用户管理这块功能，原因有二：
　　1.那些大、中型后台数据库系统软件所提供的多用户及其权限设置都是针对数据库的共有属性，并不一定能完全满足某些特例的需求；
　　2.不要过多的依赖后台数据库系统软件的某些特殊功能，多种大、中型后台数据库系统软件之间并不完全兼容。否则一旦日后需要转换数据库平台或后台数据库系统软件版本升级，之前的架构设计很可能无法重用。

　　下面看看如何自行设计一套比较灵活的多用户管理模块，即该数据库管理软件的系统管理员可以自行添加新用户，修改已有用户的权限，删除已有用户。首先，分析用户需求，列出该数据库管理软件所有需要实现的功能；然后，根据一定的联系对这些功能进行分类，即把某类用户需使用的功能归为一类；最后开始建表：
　　
功能表(Function_table)
名称　　　　　类型　　　　约束条件　　　说明
f_id          int    　   无重复　　   功能标识，主键
f_name        char(20)    不允许为空   功能名称，不允许重复
f_desc        char(50)    允许为空     功能描述

用户组表(User_group)
名称　　　　　类型　　　　约束条件　　　说明
group_id      int         无重复        用户组标识，主键
group_name    char(20)    不允许为空    用户组名称
group_power   char(100)   不允许为空    用户组权限表，内容为功能表f_id的集合

用户表(User_table)
名称　　　　　类型　　　　约束条件　　　说明
user_id       int         无重复        用户标识，主键
user_name     char(20)    无重复        用户名
user_pwd      char(20)    不允许为空    用户密码
user_type     int         不允许为空    所属用户组标识，和User_group.group_id关联

　　采用这种用户组的架构设计，当需要添加新用户时，只需指定新用户所属的用户组；当以后系统需要添加新功能或对旧有功能权限进行修改时，只用操作功能表和用户组表的记录，原有用户的功能即可相应随之变化。当然，这种架构设计把数据库管理软件的功能判定移到了前台，使得前台开发相对复杂一些。但是，当用户数较大(10人以上)，或日后软件升级的概率较大时，这个代价是值得的。


　　四、简洁的批量m:n设计
　　碰到m:n的关系，一般都是建立3个表，m一个，n一个，m:n一个。但是，m:n有时会遇到批量处理的情况，例如到图书馆借书，一般都是允许用户同时借阅n本书，如果要求按批查询借阅记录，即列出某个用户某次借阅的所有书籍，该如何设计呢？让我们建好必须的3个表先：

书籍表(Book_table)
名称　　　　　类型　　　　约束条件　　　说明
book_id       int         无重复        书籍标识，主键
book_no       char(20)    无重复        书籍编号
book_name     char(100)   不允许为空    书籍名称
……

借阅用户表(Renter_table)
名称　　　　　类型　　　　约束条件　　　说明
renter_id     int         无重复        用户标识，主键
renter_name   char(20)    不允许为空    用户姓名
……

借阅记录表(Rent_log)
名称　　　　　类型　　　　约束条件　　　说明
rent_id       int         无重复        借阅记录标识，主键
r_id          int         不允许为空    用户标识，和Renter_table.renter_id关联
b_id          int         不允许为空    书籍标识，和Book_table.book_id关联
rent_date     datetime    不允许为空    借阅时间
……

　　为了实现按批查询借阅记录，我们可以再建一个表来保存批量借阅的信息，例如：

批量借阅表(Batch_rent)
名称　　　　　类型　　　　约束条件　　　说明
batch_id      int         无重复        批量借阅标识，主键
batch_no      int         不允许为空    批量借阅编号，同一批借阅的batch_no相同
rent_id       int         不允许为空    借阅记录标识，和Rent_log.rent_id关联
batch_date    datetime    不允许为空    批量借阅时间

　　这样的设计好吗？我们来看看为了列出某个用户某次借阅的所有书籍，需要如何查询？首先检索批量借阅表(Batch_rent)，把符合条件的的所有记录的rent_id字段的数据保存起来，再用这些数据作为查询条件带入到借阅记录表(Rent_log)中去查询。那么，有没有什么办法改进呢？下面给出一种简洁的批量设计方案，不需添加新表，只需修改一下借阅记录表(Rent_log)即可。修改后的记录表(Rent_log)如下：

借阅记录表(Rent_log)
名称　　　　　类型　　　　约束条件　　　说明
rent_id       int         无重复        借阅记录标识，主键
r_id          int         不允许为空    用户标识，和Renter_table.renter_id关联
b_id          int         不允许为空    书籍标识，和Book_table.book_id关联
batch_no      int         不允许为空    批量借阅编号，同一批借阅的batch_no相同
rent_date     datetime    不允许为空    借阅时间
……

　　其中，同一次借阅的batch_no和该批第一条入库的rent_id相同。举例：假设当前最大rent_id是64，接着某用户一次借阅了3本书，则批量插入的3条借阅记录的batch_no都是65。之后另外一个用户租了一套碟，再插入出租记录的rent_id是68。采用这种设计，查询批量借阅的信息时，只需使用一条标准T_SQL的嵌套查询即可。当然，这种设计不符合3NF，但是和上面标准的3NF设计比起来，哪一种更好呢？答案就不用我说了吧。


　　五、冗余数据的取舍
　　上篇的“树型关系的数据表”中保留了一个冗余字段，这里的例子更进一步——添加了一个冗余表。先看看例子：我原先所在的公司为了解决员工的工作餐，和附近的一家小餐馆联系，每天吃饭记账，费用按人数平摊，月底由公司现金结算，每个人每个月的工作餐费从工资中扣除。当然，每天吃饭的人员和人数都不是固定的，而且，由于每顿工作餐的所点的菜色不同，每顿的花费也不相同。例如，星期一中餐5人花费40元，晚餐2人花费20，星期二中餐6人花费36元，晚餐3人花费18元。为了方便计算每个人每个月的工作餐费，我写了一个简陋的就餐记账管理程序，数据库里有3个表：

员工表(Clerk_table)
名称　　　　　类型　　　　约束条件　　　说明
clerk_id      int         无重复        员工标识，主键
clerk_name    char(10)    不允许为空    员工姓名

每餐总表(Eatdata1)
名称　　　　　类型　　　　约束条件　　　说明
totle_id      int         无重复        每餐总表标识，主键
persons       char(100)   不允许为空    就餐员工的员工标识集合
eat_date      datetime    不允许为空    就餐日期
eat_type      char(1)     不允许为空    就餐类型，用来区分中、晚餐
totle_price   money       不允许为空    每餐总花费
persons_num   int         不允许为空    就餐人数

就餐计费细表(Eatdata2)
名称　　　　　类型　　　　约束条件　　　说明
id            int         无重复        就餐计费细表标识，主键
t_id          int         不允许为空    每餐总表标识，和Eatdata1.totle_id关联
c_id          int         不允许为空    员工标识标识，和Clerk_table.clerk_id关联
price         money       不允许为空    每人每餐花费

　　其中，就餐计费细表(Eatdata2)的记录就是把每餐总表(Eatdata1)的一条记录按就餐员工平摊拆开，是个不折不扣的冗余表。当然，也可以把每餐总表(Eatdata1)的部分字段合并到就餐计费细表(Eatdata2)中，这样每餐总表(Eatdata1)就成了冗余表，不过这样所设计出来的就餐计费细表重复数据更多，相比来说还是上面的方案好些。但是，就是就餐计费细表(Eatdata2)这个冗余表，在做每月每人餐费统计的时候，大大简化了编程的复杂度，只用类似这么一条查询语句即可统计出每人每月的寄餐次数和餐费总帐：

SELECT clerk_name AS personname,COUNT(c_id) as eattimes,SUM(price) AS ptprice FROM Eatdata2 JOIN Clerk_tabsle ON (c_id=clerk_id) JOIN eatdata1 ON (totleid=tid) WHERE eat_date>=CONVERT(datetime,'"&the_date&"') AND eat_date<DATEADD(month,1,CONVERT(datetime,'"&the_date&"')) GROUP BY c_id

　　想象一下，如果不用这个冗余表，每次统计每人每月的餐费总帐时会多麻烦，程序效率也够呛。那么，到底什么时候可以增加一定的冗余数据呢？我认为有2个原则：

　　１、用户的整体需求。当用户更多的关注于，对数据库的规范记录按一定的算法进行处理后，再列出的数据。如果该算法可以直接利用后台数据库系统的内嵌函数来完成，此时可以适当的增加冗余字段，甚至冗余表来保存这些经过算法处理后的数据。要知道，对于大批量数据的查询，修改或删除，后台数据库系统的效率远远高于我们自己编写的代码。
　　２、简化开发的复杂度。现代软件开发，实现同样的功能，方法有很多。尽管不必要求程序员精通绝大部分的开发工具和平台，但是还是需要了解哪种方法搭配哪种开发工具的程序更简洁，效率更高一些。冗余数据的本质就是用空间换时间，尤其是目前硬件的发展远远高于软件，所以适当的冗余是可以接受的。不过我还是在最后再强调一下：不要过多的依赖平台和开发工具的特性来简化开发，这个度要是没把握好的话，后期维护升级会栽大跟头的。