Database Design StepbyStep (6) extract Business Rules

Introduction: in Database Design StepbyStep (5), we use multiple methods to understand the customer's requirements and write the requirement documents. 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 the importance of business processes.

Introduction: In database design Step by Step (5), we use multiple methods to understand the customer's requirements and write the requirement documents. 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 the importance of business processes.

Introduction: In database design Step by Step (5), we use multiple methods to understand the customer's requirements and write the requirement documents. 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:

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

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 rules are, the more difficult and error-prone it is to lease servers in Hong Kong.

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 ):

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

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 the rule is to test a valid value group, the Hong Kong virtual host converts the rule into 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.

Relationship between the parameter table RoyaltyRates and the Books table