資料庫設計---關於建表的時候選擇橫標和豎表(縱表)的一點思考

本文出處：http://www.cnblogs.com/wy123/p/6677073.html 在做數據統計類資料庫設計的時候，在考慮數據存儲的時候，經常會遇到邏輯上同一個BusinessID對應多個數據點的情況，比如工資表中的員工ID以及各項工資信息，財務表中的各個報表Id和多個數據點之間的信息 ...

本文出處：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