A talk about database design

Note: My development experience is still shallow, the following is mainly about their own ideas, the shortcomings please point out.

The last half of the time spent in the management system of the Open, the database design has some of its own ideas, in my opinion database design key point is a compromise. A well-designed database is considered before the business logic, design specification, and easy to develop, thus obtaining 3-win results. The following are mainly in the point of thinking and summarizing.

How to design a database with high flexibility

It can be said that the requirements are constantly changing before the project is delivered, and how to minimize the changes in the table structure as the requirements change is the problem I am now considering. For the general case, we can add some reserved fields in the design, which can be used when the requirements change, or add the insufficient fields. However, there will be some troublesome situation-the old table is not enough for modification and maintenance and requires partial redesign, which will cause more trouble for development, because most of us will use ORM now.

EG1:
In the business process of the loan, each loan application needs to be audited.
At first, there were only 2 audit roles involved, so I chose to add the fields directly to the loan application form.
After that, the number of roles involved in auditing has become much more, and the audit generated more business parameters (such as various types of attachments, opinions, etc.), the direct addition of fields to the loan table appears to be less reasonable, and not conducive to the audit process of the embodiment. Therefore, this part needs to be redesigned separately to pull the audited events out of a single table, linked to the application by a foreign key, according to the needs of the business flow. (Process complex can refer to Workflow engine design idea)

In this case, the initial design, think that since the audit of the number of people, directly add the audit information to the application form, do not need to build a table, but also facilitate the development, but later the need to change a single unprepared. It's a paradox here, and it also finds that the database design is surprisingly similar to the one set of software engineering. In software engineering, we often hear a phrase "low-coupling, cohesion-poly", generally reminds us, as far as possible to ensure that modules and modules are independent of each other, reduce unnecessary dependencies.

This is the place that bothers me, if the first design to consider a single duty, divided into two tables, it is somewhat complicated, it is not conducive to development (need to maintain 1-n relationship). After all, the code can be refactored to some extent.

Application of database principle in practical design

We have learned a lot of concepts in database theory and design because of the limitations of the experimental environment (small amount of data, simple business in class, etc.), which led to a lot of concepts that really became a rote learning concept at that time. This may come as the development experience grows. Here are a few, for them to brush a sense of existence, the content is very shallow, but also hope that in the learning database has a little help.

Normal form (NF)

1NF is defined as: each attribute in a relationship that conforms to 1NF cannot be divided
2NF is based on 1NF, eliminating the partial function dependency of non-main attribute on code.
3NF on the basis of 2NF, eliminate the non-main property of the code transfer function dependency.
...

The concept of the paradigm everyone must be very familiar with the back, I graduate interview also tested this. In fact, we have already used the database by design. To clarify the relationship between entities, drawing E-r diagram, is actually the use of the paradigm.

Indexing (Index)

Index, the interview is often asked, but when he learned that he basically does not feel his existence, after all, the test data set is too small. The underlying principle is involved in the data structure, which is not unfolded here. But what you need to know is:

• You need to do a table join field and you need to add index.
• Fields that you frequently need to query, you need to add index.
• When many fields need to be checked, you need to add a combination query.

Here is an example of a practical project that you can feel:
There is a place for data presentation, which requires multi-table search. The following are the differences between indexed and non-built indexes.

Transactions (Transaction)

A database transaction is a sequence of operations performed as a single logical unit of work , either completely or completely Transaction. Transaction processing ensures that data-oriented resources are not permanently updated unless all operations within the transactional unit are completed successfully. By combining a set of related actions into a single unit that either succeeds or all fails, you can simplify error recovery and make your application more reliable. To become a transaction, a logical unit of work must satisfy the so-called ACID (atomicity, consistency, isolation, and persistence) attributes. A transaction is a logical unit of work in a database run, which is handled by the transaction management subsystem in the DBMS.

It should be understood that the management system has been written. This is probably the model below. In JDBC, it can be implemented with annotations or configuration files in SSH or SSM.

BeginTransaction{    // to-do     commit}catch{    rollback}

Transactions are generally required for multi-table operations! For example, cascade Delete, multi-table modification. If you do not use it, it will result in inconsistent data once an exception occurs!

For details, refer to:

• MyBatis Transactions
• JDBC Control transactions

Locks (Lock)

The lock according to different method, can be divided into optimistic lock, pessimistic lock. It can also be divided into shared locks and exclusive locks.

Generally in the case of concurrency problems will be used to lock, specifically can see my other blog-the initial experience, to solve small-scale concurrent order problems. The description and implementation of the lock are described in detail.

I have limited experience, in fact, there are many similar concepts, database security, disaster recovery backup and so on have not been contacted, hope to have the opportunity to complete.

Utilization of redundant fields

The use of redundant fields is actually just for the convenience of developing a tip.

A small example can be seen in detail. A loan has to be approved by many people. So there is a table of borrowing and approving processes , and a couple of them. Through the borrowing number can go to the audit table to check all of its audit status.

At this point, I choose to deposit the Audit process table ID (foreign key) in the borrowing table, and save the current audit information for this loan. The advantage of this is that I do not need to do a table connection, directly can query to the current process of borrowing, or the application failed, the failure in which process. From the perspective of development, if there is a page that needs to show the current process of borrowing, it can be easily queried.

After all, handling table join operations is relatively cumbersome and inefficient. So with the storage conditions allowed, you can reduce the hassle of development with proper redundancy. Here is a compromise of database design, now there is a breach of redundancy is 2-nf, so sometimes need to rely on experience to adjust. for fields that are not frequently required to be modified, it is possible to properly redundant access, conversely, often need to modify the field, if redundant access, accidentally easily caused inconsistent data, it is not recommended.

View vs. redundant tables

Before we talk about redundant tables, let's take a look at some of the concepts in Java OO.

VO: The View object, which is used for the presentation layer, that encapsulates all the data for a specified page (or component).
DTO (Data Transfer object): Data Transfer object, this concept is derived from the design pattern of the Java EE, the original purpose is to provide a coarse-grained data entity for the distributed application of EJB, to reduce the number of distributed calls, thus improve the performance of distributed calls and reduce the network load. But here, I refer to the data transfer object between the presentation layer and the service layer.
Do (domain object): Domain objects are tangible or intangible business entities that are abstracted from the real world.
PO (Persistent object): A persisted object that forms a one by one mapping relationship with the data structure of the persistence layer (usually the relational database), and if the persistence layer is a relational database, then each field (or several) in the data table corresponds to one (or several) attributes of the PO.

First, these objects are actually related to the data tables in the database. What I'm trying to say is that there may be multiple tables involved in the presentation or data transfer, and all we do is create a new object and then put the persisted objects in multiple tables, or extract the required fields directly from the persisted objects of multiple tables. This object is the VO and dto mentioned above.

This approach can also be implemented in the database, that is, redundant tables. In fact, a lot of tables of data unified into a table, for the convenience of query and update.

Before encountering the following business:

There are orders, order details before the one-to-many, order and card binding, one-to-many. Now I need to show the data together, at the same time the granularity is the most granular, the order details and card number in the form of display, t at the same time can be updated.
Eg: there is Order record a, order details B1, B2, binding two cards C1, C2, then the display effect is as follows

C1 A B1C1 A B2C2 A B1 C2 A B2

This time considering that if you create a DTO to hold the data, it takes a lot of time to query the assembly, so you create a redundant table to maintain the relationship. Then through this layer of redundant table query results can be directly used to display, update the time also need to directly find the foreign key to update it.

I think the use of dtos or redundant tables depends entirely on the convenience of development and the need to persist the data.

Similarly, the previous example mentions the need to update or delete the data, if only for the presentation of the data, such as some statistical data. Then there is no need for redundant tables. The database has a proprietary concept-view. For example, query every year quarterly sales and so on, directly using SQL to create a view, query the results in days, the display of the time directly from the view of the data can be, the complex SQL statement left in the database.

Of course Create a view if you use a large number of table joins, remember to create index to optimize, otherwise the query efficiency will be very low.

Data dictionaries and code tables

Data dictionaries and code tables actually store some constants.

The data dictionary is usually two tables, one storage category and one key value pair. Data dictionaries are generally used to access some less variable data, such as gender, order status, etc. And these data volumes are smaller and can be stored uniformly.

Code tables, which are generally stored by each tube. For example, the article Classification table, label table, provincial city code, commodity classification etc. These data are large, and there are some business, some will change with the expansion of the business, so it is better to save the table alone.

Summarize

Suddenly code a lot of words, mainly write some of the database of some concepts in the actual application of the embodiment. Expressed some thoughts and insights on database design in the recent period. Man is the reed of thought, practice is the only standard for testing truth, well ...

A talk about database design