数据库设计---关于建表的时候选择横表和竖表(纵表)的一点思考

 

本文出处：http://www.cnblogs.com/wy123/p/6677073.html 

 

在做数据统计类数据库设计的时候，在考虑数据存储的时候，经常会遇到逻辑上同一个BusinessID对应多个数据点的情况，
比如工资表中的员工ID以及各项工资信息，财务表中的各个报表Id和多个数据点之间的信息
面对这种情况，如何来设计表结构，是横表，还是竖表，各有那些优缺点，本文将做一个粗浅的分析。

 

横标和竖表的表现形式

日常生活中也有很多类似的例子，先用一个Excel画一个例子，比如工资表
这么做就是“横表”，特点是，一个ID对应所有的值信息，以行Key-Value1-Value2-Value3的方式存储

如下是竖表（纵表），特点是每行仅存储该ID的某一个类别字段的值，以行的方式存储Key-Value的方式存储

 

横标和竖表的设计示例

　　下面通过一个具体的例子来说明横标和竖表的一些特点

--横标
CREATE TABLE HorizontalTable
(
    Id                int identity(1,1),
    BusinessId        varchar(50)         ,
    CategoryVal1    varchar(20)         ,
    CategoryVal2    decimal(20,5)     ,
    CategoryVal3    datetime         ,
    CategoryVal4    varchar(20)         ,
    CategoryVal5    varchar(20)         ,
    CategoryVal6    varchar(20)
)
insert into HorizontalTable  values ('BH000001','value1',89.12,'20170406','abc4','abc5','abc6')
insert into HorizontalTable  values ('BH000002','value2',99.11,'20170407','abc4','abc5','abc6')

--竖表
CREATE TABLE VerticalTable
(
    Id                int identity(1,1),
    BusinessId        varchar(50),
    CategoryKey        varchar(20),
    Val                varchar(20)
)
insert into VerticalTable values ('BH000001','CategoryKey1','values1')
insert into VerticalTable values ('BH000001','CategoryKey2',89.12)
insert into VerticalTable values ('BH000001','CategoryKey3','20170406')
insert into VerticalTable values ('BH000001','CategoryKey4','abc4')
insert into VerticalTable values ('BH000001','CategoryKey5','ab5')
insert into VerticalTable values ('BH000001','CategoryKey6','ab6')
insert into VerticalTable values ('BH000002','CategoryKey1','values2')
insert into VerticalTable values ('BH000002','CategoryKey2',99.12)
insert into VerticalTable values ('BH000002','CategoryKey3','20170407')
insert into VerticalTable values ('BH000002','CategoryKey4','abc4')
insert into VerticalTable values ('BH000002','CategoryKey5','abc5')
insert into VerticalTable values ('BH000002','CategoryKey6','abc6')

横表中的数据：

竖表中的数据

　　

可能实际应用中，要比这个示例中的情况更加复杂，那么在设计表结构的时候，如何选择横标或者竖表？
首先来看横标的特点

对于横表
　　1，同一个Key值对应的列是固定的，比如，比如HorizontalTable中有6个字段
　　2，各个字段的值是自由的，比如HorizontalTable中的CategoryVal1是varchar类型的，CategoryVal2是decimal的
　　3，表中并不存储描述性字段本身（相比纵表）
　　4，相比竖表，存储同样多的数据，行数要少
对于竖表
　　1，同一个Key值对应的列是动态的，因为是按照行存储的，可以存储成Key1—Value1，Key1—Value2，Key1—Value3的方式存储
　　2，字段的类型是固定的,但是类似是要兼容的，不能有个性化的字段，比如VerticalTable中的CategoryKey+Val，因为固定了这么一个字段
　　3，表中需要存储描述字段本身（相比横标），要根据BusinessKey值的不同，重复存储CategoryKey
　　4，相比横表，存储同样多的数据，行数要多

综上可以看出，
　　横标的优点：横标的有点事显示的较为清晰直观，同时在字段的选择上更为科学合理，具体的字段可以根据具体情况划分字段类型，
　　横标的缺点：不方便扩展和公用，也就是说设计了一张横标，只能在固定的某一种特定的相对不变的场景下使用，
　　　　　　　　比如加字段，或者类似的业务想公用一张横表，都有局限

　　竖表的优点：最大的特点是可以灵活扩展存储的内容，同时具有一定的公用性
　　　　　　　　因为竖表的存储结构不受字段个数的限制，可以存储具有一定共性的业务数据。
　　竖表的缺点：竖表的字段类型要兼容，比如横标可以根据具体的值设计成varchar，decimal，datetime等，
　　　　　　　　横标为了兼容以上字段类型，只能设计成varchar的，可能会浪费一定的空间

 

　　横标和竖表主要考虑的是扩展性和共同性，对于显示方式问题，个人认为倒是问题不大，无非是行转列和列转行的问题
　　如下是一个将上述设计的横表转竖表和竖表转横标的示例，也不复杂，因此说，显示的问题不是大问题

select * from HorizontalTable
--列转行
;WITH HorizontalCET
AS
(
    SELECT Id,BusinessId,CategoryVal1,
            cast(CategoryVal2 as varchar(20)) as CategoryVal2,
            cast(CategoryVal3 as varchar(20)) as CategoryVal3,
            CategoryVal4,
            CategoryVal5
    FROM HorizontalTable
)
SELECT Id,BusinessId,ColumnName,ColumnVal
FROM HorizontalCET
UNPIVOT (ColumnVal FOR ColumnName IN 
            (CategoryVal1,
            CategoryVal2,
            CategoryVal3,
            CategoryVal4,
            CategoryVal5)
        ) tmp

--列转行
select * from VerticalTable
SELECT * FROM 
(
    select BusinessId ,
           CategoryKey,
           Val 
    from VerticalTable
)t
 PIVOT( MIN(Val) FOR CategoryKey IN (CategoryKey1,
                                    CategoryKey2,
                                    CategoryKey3,
                                    CategoryKey4,
                                    CategoryKey5,
                                    CategoryKey6)
)a

　　

 

关于横表和竖表的性能问题

　　关于性能问题，很难一概而论，还要结合具体的情况作分析，比如查询方式，查询数据了，索引结构等等都有一定的关系。
　　表面上看，竖表存储了大量冗余的数据，浪费了一定量的磁盘空间是事实，但是极端情况下横表也有可能造成极大的空间浪费
　　了解SQL Server的同学肯定知道，
　　SQL Server中正常来来说是行存储，一行数据不能跨页存储（当然forwarded存储方式的数据除外，有机会说这个），
　　SQL Server的最小存储单位是页（Page），一个页的大小是8kb，除去page信息固定占用的空间之外是8060个字节，
　　每一行固定的一行数据除了数据自身占用的空间外，至少（不是一定，表结构越复杂占用的额外空间越大）还要占用1+1+2+2+1=7个字节

　　对于宽表，一旦字段长度达到一定的程度，
　　比如每行长度为800个字节，理论上将，在一个page上，存储9行记录之后，还剩余800字节的空间（具体剩余多少跟表结构有关，这里只是举例说明），
　　对不起，第十行数据来了已经存不进去了，只能新开页面分配存储空间，这样，当前这个页面就浪费了800字节的存储空间
　　反观竖表，因为存储的数据行都非常短，即便发生上述情况，也只会浪费很少的一点数据空间（小于一行数据的空间）
　　极端情况下会更加有意思，参考这个http://www.cnblogs.com/studyzy/archive/2008/11/27/1342003.html

 　　

　　从读取的另外角度来看，大多数情况下，建表的方式都是行存储，意味着每一行的数据是存储在一起的（字段大的时候当然可以跨页面），单个页面存储的数据行数就变得较少
　　sqlserver读取数据的时候是按照行来读取的，不管你查询几个字段，最终都是要将整个行的数据读取出来，
　　而存储的最小单元是页，也就是page，
　　如果一个表的字段非常多，那么一次查询，即便是需要这些字段中的一部分，也要将所有的字段读取出来，这意味着，你读取的行数多的话，读取出来的不需要的字段也将变得更多
　　比如一个表中设计了30个字段，正常情况下，一个查询只需要读取6个字段，即便是这样，sqlserver在执行查询的时候依然读取的是30个字段出来，
　　这样的的话，读取同样多的数据，就可能需要读取更多的页才能完成这个查询
　　如果是设计成竖表,根据具体的Id来，如果有合理的索引，使用索引就可以完成查询，而不需要再去读表的page，这样就可以避免横表读取时候的这种情况。

　　有上述可见，对于横表和竖表，不管是设计上还是存储上，优点和缺点都是看站在哪个角度来看的，
　　从一个角度来看是有点，从另外一个角度看就可能会变成缺点，只有舍弃一部分，根据实际情况权衡之后做出取舍。
　　凡事无绝对，适合即可。

 

总结：

　　本文从适应场景、存储、性能等方面粗浅第分析了表设计时候横标和竖表的特点和优缺点，
　　具体设计的时候可综合考虑，做出合理的选择。
　　另外，本文肯定还有没有预计或者说想到的情况以及评估方向，也希望有想法的同学补充，谢谢。