Database Design---A little thought on choosing banner and vertical table (longitudinal table) when building a table

The source of this article: http://www.cnblogs.com/wy123/p/6677073.html

When doing data statistics database design, when considering the data storage, often encounter logically the same businessid corresponding to multiple data points of the situation,
For example, the employee ID in the payroll and the payroll information, the individual report IDs in the financial table, and the information between the multiple data points
In the face of this situation, how to design table structure, is a horizontal table, or vertical table, each has those advantages and disadvantages, this article will do a superficial analysis.

Representation of banner and vertical tables

There are a lot of similar examples in daily life, first using an Excel to draw an example, such as salary table
This is a "horizontal table", characterized by an ID that corresponds to all the value information stored in a row key-value1-value2-value3 way

The following is a vertical table (the vertical table), characterized by the value of only one category field for which the ID is stored per row, stored as a row in key-value way

Design examples for banner and vertical tables

Here is a concrete example to illustrate some of the features of banner and vertical tables

--BannerCREATE TABLEhorizontaltable (Idint Identity(1,1), Businessidvarchar( -), CategoryVal1varchar( -), CategoryVal2decimal( -,5), CategoryVal3datetime, CategoryVal4varchar( -), CATEGORYVAL5varchar( -), CategoryVal6varchar( -))Insert  intoHorizontaltableValues('BH000001','value1',89.12,'20170406','ABC4','Abc5','ABC6')Insert  intoHorizontaltableValues('BH000002','value2',99.11,'20170407','ABC4','Abc5','ABC6')--Vertical TableCREATE TABLEverticaltable (Idint Identity(1,1), Businessidvarchar( -), Categorykeyvarchar( -), Valvarchar( -))Insert  intoVerticaltableValues('BH000001','CategoryKey1','values1')Insert  intoVerticaltableValues('BH000001','CategoryKey2',89.12)Insert  intoVerticaltableValues('BH000001','CategoryKey3','20170406')Insert  intoVerticaltableValues('BH000001','CategoryKey4','ABC4')Insert  intoVerticaltableValues('BH000001','CategoryKey5','AB5')Insert  intoVerticaltableValues('BH000001','CategoryKey6','ab6')Insert  intoVerticaltableValues('BH000002','CategoryKey1','Values2')Insert  intoVerticaltableValues('BH000002','CategoryKey2',99.12)Insert  intoVerticaltableValues('BH000002','CategoryKey3','20170407')Insert  intoVerticaltableValues('BH000002','CategoryKey4','ABC4')Insert  intoVerticaltableValues('BH000002','CategoryKey5','Abc5')Insert  intoVerticaltableValues('BH000002','CategoryKey6','ABC6')

Data in a horizontal table:

Data in a vertical table

　　

It might be more complicated than the case in the actual application, so how do you choose banner or Vertical table when designing the table structure?
First, look at the features of banner

For horizontal tables
1, the column corresponding to the same key value is fixed, for example, there are 6 fields in a horizontaltable
2, the values of each field are free, such as CategoryVal1 in horizontaltable is varchar type, CATEGORYVAL2 is decimal
3, the table does not store the descriptive field itself (compared to the vertical table)
4, save as much data as the vertical table, less rows
For vertical tables
1, the column corresponding to the same key value is dynamic because it is stored as a row and stored as a key1-value1,key1-value2,key1-value3.
2, the type of the field is fixed, but similar to be compatible, can not have a personalized field, such as verticaltable in the Categorykey+val, because the fixed a field
3, the table needs to store the Description field itself (compared to banner), to the Businesskey value according to the different, repeat storage Categorykey
4, more data stored than horizontal table, more rows

As you can see,
Banner Advantages: Banner A little things to show a clearer and more intuitive, while in the field of the choice of more scientific and reasonable, specific fields can be divided according to the specific circumstances of the field type,
Banner disadvantage: Inconvenient to expand and public, that is to say, designed a banner, can only be fixed in a certain specific relative to the situation of use,
For example, add fields, or similar businesses want to share a horizontal table, there are limitations

Advantages of the vertical table: The biggest feature is the flexibility to expand the content of the storage, while having a certain degree of common
Because the storage structure of a vertical table is not limited by the number of fields, you can store business data with a certain commonality.
The disadvantage of the vertical table: The field type of the vertical table to be compatible, such as banner can be designed according to the specific value of varchar,decimal,datetime, etc.
Banner in order to be compatible with the above field types, can only be designed as varchar, may waste a certain amount of space

Banner and vertical tables are mainly about extensibility and commonality, for the display mode of the problem, personally think it is not a problem, nothing more than row to column and the issue of career change
The following is an example of the design of the horizontal table to the vertical table and the vertical table to banner, and it is not complicated, so that the problem is not a big problem to display

Select *  fromhorizontaltable--Row Career; withHorizontalcet as(    SELECTId,businessid,categoryval1,cast(CategoryVal2 as varchar( -)) asCategoryVal2,cast(CategoryVal3 as varchar( -)) asCategoryVal3, CategoryVal4, CATEGORYVAL5 fromhorizontaltable)SELECTId,businessid,columnname,columnval fromHorizontalcetunpivot (Columnval forColumnNameinch(CategoryVal1, CategoryVal2, CategoryVal3, CategoryVal4, Cate GORYVAL5)) TMP--Row CareerSelect *  fromverticaltableSELECT *  from (    SelectBusinessid, Categorykey, Val fromverticaltable) T PIVOT (MIN(Val) forCategorykeyinch(CategoryKey1, CategoryKey2, CategoryKey3,                                    CategoryKey4, CategoryKey5, CATEGORYKEY6)) a

　　

About performance issues with horizontal and vertical tables

About the performance problem, it is difficult to generalize, but also to combine the specific situation for analysis, such as Query method, query data, index structure and so on have a certain relationship.
On the surface, the vertical table stores a large amount of redundant data, wasting a certain amount of disk space is a fact, but in extreme cases the horizontal table can also cause great space waste
Understand that the students of SQL Server must know that
In SQL Server, it's normal to be a row store, one row of data that can't be stored across pages (except for data that's forwarded stored),
The minimum storage unit for SQL Server is the page, and the size of a page is 8kb, except that the page information is 8,060 bytes away from the fixed space occupied.
Each row of fixed rows of data, in addition to the amount of space occupied by the data itself, at least (not necessarily, the more complex the table structure, the greater the additional space) also consumes 1+1+2+2+1=7 bytes

For a wide table, once the field length reaches a certain level,
For example, the length of each line is 800 bytes, in theory, on a page, after storing 9 rows of records, the remaining 800 bytes of space (how much of the remainder is related to the table structure, here is just an example),
Sorry, the tenth row of data has been saved, only the new page allocation of storage space, so that the current page is a waste of 800 bytes of storage space
View the vertical table, because the data rows stored are very short, even if this happens, it will only waste a little bit of data space (less than a row of data space)
More interesting in extreme cases, refer to this http://www.cnblogs.com/studyzy/archive/2008/11/27/1342003.html

As can be seen above, for the horizontal table and the vertical table, whether it is design or storage, the advantages and disadvantages are to look at the point of view,
From a point of view is a bit, from another point of view may become a disadvantage, only to abandon part, according to the actual trade-offs.
Everything is not absolute, suitable can be.

Summarize:

This paper analyzes the characteristics and advantages and disadvantages of banner and vertical tables in the design of table from the aspects of adapting to scene, storage, performance, etc.
Specific design time can be considered comprehensively, make a reasonable choice.
In addition, this article certainly has not expected or said that the situation and assessment direction, but also hope that the students have ideas to add, thank you.

　　　　

Database Design---A little thought on choosing banner and vertical table (longitudinal table) when building a table