Database reconstruction series (1)

Database reconstruction discussion series

(1) Basics

 

1. Database reconstruction is divided into six types:

2. Database taste
Similar to the concept of "code taste", code taste is a common problem in code, indicating that reconstruction is required.
Database taste indicates that the database needs to be restructured. These flavors include:

(1) multi-purpose Columns
If a column is used for multiple purposes, additional code may exist to ensure that the source data is used in the correct way, these codes often check the values of one or more other columns.
For example:
A column is used to store a person's birthday if the person is a customer. If this person is a company employee, this column is used to store the date of entry into the factory.

 

(2) multi-purpose tables
If a table is used to store several types of entities, there may be design defects.
For example:
A table customer stores both company and company information.

(3) duplicate data
Duplicate data is a serious problem for operational databases, because, if the data is stored in a few places, the possibility of inconsistency increases.

(4) tables with too many columns
If a table contains too many columns, it indicates that the table lacks cohesion.
For example:
The customer table contains three different addresses (shipping address, bill address, company address) and several phone numbers (home phone, work phone, mobile phone number, etc ), you may need to standardize the structure and add it to the address and phonenumber tables.

(5) "smart" column
The "smart" column is such a column, where different data locations represent different concepts.
For example:
The first four digits of the customer ID represent the customer's bank, and the customer ID is a "smart" column. Because you will parse it to get more fine-grained information, such as the account ID.

3. Database Reconstruction
Database reconstruction is a database implementation technology. Similar to code reconstruction, database schema reconstruction makes it easy to add something on it.

Provides high-level views of key development activities that take place in modern projects involving object and relational database technology. It is necessary to iterate back and forth between these activities.

Database reconstruction is an important part of evolutionary database development. Data Modeling also needs to be performed in an evolutionary/agile manner.
The more coupling, the more difficult it is to refactor. This is true for code and database refactoring.
The simplest scenario is a single application database. Because the database schema is only coupled with itself and an application.
In a multi-application database architecture, your database schema may be associated with the application source code, persistent framework, Orm tools, and other databases (providing replication, data extraction, and loading), data file schema, test code, and even the database itself.

One effective way to reduce database coupling is to encapsulate access to the database. Allows external programs to access the database through the persistence layer, which can encapsulate database access.
The persistent layer has multiple implementation methods:
(1) using the Data Access Object Dao, it implements the required SQL code;
(2) Adoption of the Framework;
(3) stored procedures;
(4) use web services.

Coupling can never be reduced to 0, but it can certainly be managed.