A Concise database design pattern

When designing a database storage mode, carefully analyze the data schema and don't put all the data together. Otherwise, the availability of the system, efficiency, scalability will have a serious impact. Of course, the system you design is very small and can be used in the simplest way.

Through the business proficiency, from different angles to the data for multi-dimensional analysis, can generally be analyzed from the following several directions:

1. Data flow

2. Data Access Features

3. Size of data volume

4. Amount of data growth

5. Life cycle of data

According to the above data characteristics, the data table is categorized by the comprehensive data model:

1. Constant tables

2. Incrementing a table

3. Water meter

4. Status table

5. Core table

6. Process table

In our model design of large data volume systems, we must follow these key points according to different data tables.

Core table:

The core table is the most frequent system access, in the design to consider the cost of access, must follow the paradigm, pay attention to the number of fields and field length, pay attention to the scope of the query. If the data volume of the core table is large, the data should be archived according to the partition table or table route, so as to guarantee the performance of the core table.

Process table:

The process table as the name implies is used to record a process, generally refers to the life cycle of the data, in the design process table to design a clear representation of the data life cycle of the field, for the Data Warehouse system is to make reasonable use of life cycle field, can efficiently statistical data of different life cycle, in the design of the table should also consider the cost , the cost of inserting is minimal, the modification needs to retrieve the data to retain the modified field values, and the most expensive way to delete the entire record is to retain it.

Constant tables:

The constant table almost rarely changes, similar to our use of the dictionary table, when designing such a table, to design the table parameters, smaller tables are not recommended to index.

Increment table:

Increment table growth is very fast, not all of the data is commonly used, so the size of the partition to be as balanced as possible, strictly distinguish between core data and process data, the index of the key value selectivity as high as possible, using the composite index carefully, according to the correlation relationship design appropriate partition and index.

Water table:

Water table similar record log, record some water information, water table data volume is generally large, the information is almost no change. You should pay attention to the granularity and selection of partitions at design time, and it is generally not recommended to build too many indexes.

Status table:

A state table generally refers to the process of recording a state of a behavior, a short life cycle, and easily confused with a process table. They can be distinguished simply by the fact that the state table is the trajectory of the action, and the process table is the life cycle of the data.

This article is from "Joe TJ" blog, be sure to keep this source http://joetang.blog.51cto.com/2296191/1679886

A Concise database design pattern