Database design step by step (6) -- extract Business Rules

Introduction: Database Design
Step by step (5)-understanding user requirements we use a variety of methods to understand customer requirements and write the requirements document. This article will answer three questions. 1. Why are business rules very important. 2. How to identify business rules. 3. Modify the relational model and isolate the business rules.

 

What are business rules?

Business Rules describe important objects, relationships, and activities worth recording in the business process. This includes processes, specifications, and policies in business operations. Business rules ensure that the business can meet its goals and obligations.

Some business rules in life may be:

1. When a customer enters the store, the recent employee must say "welcome to XXX" to the customer ".
2. When the customer exchanges coupons of more than 200 yuan, the clerk must check and copy the customer's ID card. If the coupon amount is less than 25 yuan, you do not need to sign it.
3. In the morning, the first person to enter the office needs to open the heat button of the water dispenser.

In this series, we focus on database-related business rules. Some examples are as follows:

1. The customer's record is created only when the customer generates the first order.
2. If a student does not select any course, set the Status field to inactive.
3. If a salesperson sells 10 sofa sets in a month, the reward is 500 yuan.
4. A contact must have at least one phone number and one email address.
5. If the total tax deduction for an order exceeds 1000 yuan, a 5% discount will be incurred.
6. If the total tax deduction for an order exceeds 500 yuan, no freight is charged.
7. Employees can enjoy a 5% discount for purchasing the company's products.
8. If the inventory of a certain item in the warehouse is lower than the total amount sold in the previous month, you need to purchase the goods.

From the database perspective, business rules are a constraint. Simple constraints include:

All orders must have a contact number.

The preceding simple rules can be easily mapped to the relational database definition to determine the data type for the field or set a field as required (not null ). The constraints expressed by some business rules are more complex, such:

The daily course time and project time of students must be between 1 and 14 hours.

We can use check constraints or foreign key constraints to implement such business rules. For some very complex business rules, such:

A instructor shall have no less than 30 hours of work per week, including office hours, lab hours, and class hours. The course preparation takes 0.5 hours every hour. One hour of lab preparation is required. The time for Guiding Students' papers should be no less than 2 hours per week.

Similar to the preceding business rules, you need to collect data from multiple tables. Therefore, it is most appropriate to implement it in the program code.

 

Identify key business rules

Recording and classifying all business rules can help us better implement business logic in the system.

How to implement business rules is not only related to the current business logic, but also to how the business logic will change in the future. When a rule is likely to change in the future, we need to build it in a more complex but flexible way.

For example, assume that the company can only deliver goods to cities with warehouses in the local area, including Nanjing, Changsha, Xi'an, and Guangzhou. Business Rules require that the city of delivery field in the order must be one of NJ, Cs, XA, and GZ.

We can simply implement this rule as a check constraint. However, if the company has a new warehouse in Shanghai in the future, it must modify the check constraint from the background database. If the company subsequently sets up more new warehouses or business rules change to be able to deliver goods to cities without warehouses, we need to modify this constraint every time.

Another method to implement this business rule is to use a foreign key. Create a shippingcities table with the following values: NJ, Cs, XA, and GZ. Then, let the foreign key of the shipping City field in the order Table reference the primary key in the shippingcities table. In this case, the shipping city column of the order can only accept cities in shippingcities. When the supported shipping cities increase or decrease, you only need to insert or delete records in shippingcities.

The implementation of the two methods is not difficult, but the previous method needs to modify the database structure each time, and the latter only needs to modify the data. Modifying data not only saves effort but also reduces technical requirements.

The preceding business rule implementation constraints may be as follows:

Shippingcity = 'nj 'or shippingcity = 'cs' or shippingcity = 'xa 'or shippingcity = 'gz'

The above code is not complex, but only programmers familiar with the database can modify it from the background. The data in the shippingcitis table is relatively easy to understand. We can provide an interface for users to maintain their own cities.

To identify key business rules, we can ask ourselves two questions.

First, how difficult it is to modify rules. The more complex the rule, the more difficult it is to modify and more prone to errors.

Second, the possibility of rule changes. Rules with frequent changes require additional design to better cope with future changes.

Rules that require special attention (key business rules ):

1. Enumeration value. For example, valid shipping cities, order statuses (pending, approved, and shipped.
2. Calculate parameters. For example, free shipping is required for orders of more than 500 RMB. This value may be adjusted to 300 yuan or 600 yuan in the future.
3. Valid parameter. For example, a project team consists of two to five members. Is it possible for one person to complete some projects or more people to participate.
4. Cross record and table check. For example, the number of items that can be ordered in an order cannot exceed the current inventory of the item.
5. General constraints. If we plan to apply similar constraints in the future, we can consider abstracting these constraints for management. For example, an insurance company recently launched insurance product. A 1000 yuan prize will be given to sales staff who can sell 20 A products each month. Different promotion and reward rules may apply to different insurance products in different time periods. We can extract the product name, number, sales volume, bonus amount, and promotion period and place them in an independent table as the parameter for calculating the bonus.
6. Very complex checks. Some check rules are very complex, so it is easier and clearer to put these rules in the program code. For example, the premise for students to select the predicate calculus course of the College of Science is that they have passed the proposition calculus course of the College of Science, the logic I and II courses of the College of Social Sciences, or the instructor's permission is required. This rule can be implemented through table-Level Check constraints in some database products, but it is easier to maintain and understand in programs.

Some business rules that can be directly implemented in the database:

1. Fixed enumerated values. For example: Gender (male, female), hand habits (left-handed, right-handed ).
2. Data Type requirements. Determining the Data Type of each field is an important feature of relational databases. Misuse of common data types (such as string) can damage performance and data error prevention.
3. Required value. For example, a member must have mobile phone contact information.
4. Rationality check. The range set for the rationality check remains unchanged. For example, the price of a product is greater than or equal to 0.

Do not reject or avoid changes as software practitioners. The only thing that remains unchanged in the world is change. When collecting business rules, you need to learn more about the business background and historical changes of the rules, rather than forcing customers to ensure that the Rules do not change. Discover and record all business rules as much as possible. These business rules are categorized based on the possibility of change and the difficulty of modification, and those that may change in the future are carefully designed.

 

Extract key business rules

After identifying and classifying business rules, we need to implement key business rules in the database or outside the database. We can refer to the following method:

1. If a rule is used to test a set of valid values, the rule is converted to a foreign key constraint. The effective shipping city in the previous example is a good example. Create the shippingcities table and enter the allowed shipping cities. Set the shippingcity column of the orders table as a foreign key and reference the primary key of the shippingcities table.

2. If the rule is a formula that may change parameters, extract these parameters to a table. For example, a salesperson who sells a car with a total price of more than 1 million yuan within a month can receive a 500 yuan bonus. Extract the parameters 1 million yuan and 500 yuan to a table. If necessary, you can even extract the period of one month as a parameter.

I have also seen some software systems with a common parameter table in the database. This common parameter table stores various system parameters, some of which are used for calculation, some are used as tests, and others determine system behavior. Each record has two fields: Name and value. For example, to determine how much bonus a salesperson can earn, we first need to find the record with the name field as bonussales and check whether the sales volume of the salesperson has reached the value field, if the answer is yes, find the record whose name field is bonusaward to determine the bonus amount. Another benefit of this design is that when a program starts, the common parameter table can be read into a collection of memory, and then you do not need to connect to the database again when using the parameter value.

3. If the logic or computing rules are complex, extract them to the Code for implementation. The Code mentioned here can be application code or database stored procedure. Putting rules in the code means that the business rules are separated from the database table structure. Changes in rules do not affect the database table structure. It is easier to maintain complex rules through structured or object-oriented programming.

Here is a comprehensive example:

The royalty of the first 5000 books sold for a database design book is 5%, the royalty between 5000 books and 10000 books is 7%, and the royalty after more than 10000 books is 10%, different types of books may have different royalties.

The preceding rules are complex and contain multiple parameters that may change. Therefore, 1st and 2 methods are used. We can implement this rule through stored procedures, and isolate parameters into a single parameter table for maintenance. The created parameter table is royaltyrates and is associated with books through bookid (1 ). In this way, it is very easy to create new royalty rules for different books.

Figure 1
Relationship between the parameter table royaltyrates and the books table

The concept of multi-layer applications is no stranger to everyone. Layer-3 applications are the most common layering method. Complex business logic is generally implemented in the middle layer (Business Layer. For some basic verifications, such as required information and number effective ranges, You need to perform dual checks on the top user interface and the bottom database. The constraints on the Database End prevent dirty data from entering the system's last line of defense. The inspection on the user interface can avoid the rejection of erroneous data transmitted to the system's backend, saving system resources.

Note: For more information about multi-tier applications, see the "summary and reference" section at the end.

 

Overview

1. Business Rules determine how the business runs, from simple and clear entry logging to complex bonus calculation formulas.

2. For databases, business rules will affect the data model. The business rule determines the field (value type and range) of each field, whether it is required, and other conditions to be met by this field.

3. It is critical to understand business rules and identify key rules that require special processing.

4. Some rules are simple and basically unchanged. They can be easily implemented using database features. Other rules may be complex or change frequently. We can isolate them from the database logically or physically (to the parameter table, stored procedure, or business layer ), make them easy to modify.

Multi-layer application reference

1. Understanding of enterprise-level system architecture (http://www.cnblogs.com/liping13599168/archive/2011/05/11/2043127.html)

2. multitier architecture (http://en.wikipedia.org/wiki/Multitier_architecture)

3. Software Architecture, artificial TS and artificial Ting (http://www.bredemeyer.com /)

Original article: http://www.cnblogs.com/DBFocus/archive/2011/06/08/2075795.html