走近复杂数据库计算型软件的设计与制作(3)—视图的设计
摘要：接着昨天的数据库的数据表的设计,今天叙述数据视图的设计。
数据视图的设计看似简单，不就是一个Select语句吗？是的，就是一个SQL语句。在这个SQL语句中包含了需要选择的列和相关联的表之间的连接关系，有的是左连接，有的是右连接，有的是全连接。这些关系大部分的教科书上都有，但是如何才能用好却不是那么简单。在连接类型的选择上需要根据业务规则来定，要靠具体情况具体应用。下面讲解一下我在数据视图设计中遇到的一些问题。
首先讲解以下Distinct的问题，视图是一个查看数据的定义，也就是经常所说的虚表。在查看数据时，会自动消除重复，也就是说如果多行完全相同的情况下，视图会保留其中的一行，而把其他的行过滤掉，笔者曾经遇到这样的问题，使用服务器同时注册5条记录，注册的时候获得系统的默认时间，其中有两条记录注册之后系统的时间值竟然完全相同。在展示数据的时候只出现了4条，导致客户的抱怨。所以建议如果想完全选择所有的符合条件的记录，不妨把主键也包含近来，即使他没有用处。有的时候，本来想要其中的一条记录，偏偏选择出来的是多条重复的记录，这在什么情况下出现，笔者也没有总结出规律，但是有一个解决办法就是使用Distinct可以消除重复。下面就使用Distinct的创建视图的定义
create view shop_view
with encryption
as
select m.m_id,sh.rm_id,sh.enabled,m.name+'('+m.m_id+')' as result
from shop sh,member m
where sh.m_id=m.m_id
union
select distinct m.m_id,s.rm_id,s.enabled, m.name+'('+m.m_id+')'
from member m,stock s
where m.m_id=s.m_id
and m.user_level=4
go
视图在创建的时候添加with encryption选项可以使得你的SQL定义不会被别人看到，增加安全性。对于程序员，只提供视图的定义而非原始表也减少了被攻击的概率。
整个系统中最关键的就是奖金的查询，上一节说过，奖金类型繁多，使用了两个表来解决存储问题，奖金表可以被看作是竖表，而分类显示需要把表结构给横过来，也就是说把一个表由竖表变成一个横表， 就需要使用同一个表的别名进行一大串的表连接。为了避免这个问题，在了解业务的需求之后，笔者把几种类型的奖金综合起来，创建视图，下面是相关的SQL语句
create view oldplan_award
with encryption
as
select *
from award_level
where award_type not in(0,1,3,5,6,7,9,10,37,38,39)
go
分类后要综合查询总数:
create view commendMemberAward_view
with encryption
as
select m_id,turns,sum(money)as award_num
from award_level
where award_type=10
and enabled=1
group by m_id,turns
go
create view ShopRentalAwardB_view
with encryption
as
select m_id,turns,sum(money)as award_num
from award_level
where award_type in(37,38,39)
and enabled=1
group by m_id,turns
go
create view educationShopAwardB_view
with encryption
as
select m_id,turns,sum(money)as award_num
from award_level
where award_type=7
and enabled=1
group by m_id,turns
go
分类之后需要综合成一个横表
如下定义，因为有可能是空值所以进行了处理
create view shopAwardB_view
with encryption
as
select a.m_id,a.turns,
case when a1.award_num is null then 0 else a1.award_num end as commendAward,
case when a2.award_num is null then 0 else a2.award_num end as rentalAward,
case when a3.award_num is null then 0 else a3.award_num end as educationAward
from award_else a left join commendMemberAward_view a1 on(a.m_id=a1.m_id)
left join ShopRentalAwardB_view a2 on(a.m_id=a2.m_id and a.turns = a2.turns)
left join educationShopAwardB_view a3 on(a.m_id=a3.m_id and a.turns = a3.turns)
where a.c_times>0
go
另外有一个视图，其作用是提供一个显示树状结构的数据，比如三叉树或者其他多叉树，每行都是一个节点相关的信心，当前的节点ID，上级节点ID,需要显示的信息。定义如下：
create view matrixGraph_view
with encryption
as
select s1.s_id,s1.ps_id,s1.m_id,s1.enabled,m.name+'('+m.m_id+')' as result,s1.position-head+1 as position
from stock s1,member m,stock s2,misc
where s1.m_id=m.m_id
and s1.ps_id=s2.S_ID
go
在这里有一点需要说明，result列对应的数据是显示在节点上的，如果客户的需求发生变化，比如要显示的内容包括ID号，在此只需要改变视图的定义即可，不用改变界面程序，或者数据表的定义，从而隔离了数据表和程序，稍微有点3层架构的思想。下面的图显示了上面的定义，如果客户需要显示Position信息，那就在result 列的定义上修改即可。
今天就先到这里，明天接着讲述函数的设计，如果你能学到一些东西或者指出笔者的一些不足之处，笔者会十分高兴。
本文转自凌辉博客51CTO博客，原文链接http://blog.51cto.com/tianli/42594如需转载请自行联系原作者
lili00okok