Design of interface transmission for relational databases in enterprise-level development

In enterprise-level development, relational databases are designed for interface transmission. This article takes oracle database as an example to discuss some design ideas about using databases for intermediate table data transmission during enterprise-level development. You are welcome to leave a message to correct your request. 1. The principle of who controls the definition of privilege is that the receiver defines the interface data lattice.

In enterprise-level development, relational databases are designed for interface transmission. This article takes oracle database as an example to discuss some design ideas about using databases for intermediate table data transmission during enterprise-level development. You are welcome to leave a message to correct your request. 1. The principle of who controls the definition permission on both sides of the interface is that the receiver is responsible for defining the interface data lattice.

Design of interface transmission for relational databases in enterprise-level development

Thinking means thinking.

This article takes oracle database as an example to discuss some design ideas about using databases for intermediate table data transmission during enterprise-level development. You are welcome to leave a message to correct your request.

1. Who will control the definition right on both sides of the interface?

Generally, the receiver is responsible for defining interface data formats and rules. Because the receiver must be very clear about what data it wants to receive. If you don't know this, you don't have to do it. Second, it also involves data conversion. It is reasonable for the sender to convert some file data that cannot be recognized by the receiver into the file data that can be recognized by the receiver and then send it to the receiver. In this way, the interactive data is a complete data that can be received, rather than a semi-finished data.

However, there are two special cases: the 1st Special Cases follow the principles of law of war, and the first step is strong. Although a project team is in the role of a data receiver, the project team that comes in early has developed and defined the sending interface, so in order to avoid repeated efforts, the receiver can only define the rules for the sender.

In the 2nd special cases, the receiver is a relatively complete system with relatively simple basic data, while the sender is a small system. This is the inversion of control caused by the basic data conversion problem.

Some companies have messy basic data. For example, system A defines A set of personnel codes, all starting with the letter. System B defines a set of personnel codes, all starting with a letter B... And so on. In fact, the human code in each system has A 1-1 correspondence, but this correspondence is only maintained in main system. At this time, system A and system B provide personnel interfaces, requiring system B to transmit personnel data. If the receiver is responsible for defining the interface, system A wants system B to convert the Personnel code to the format of system A and then send it to system. However, system B cannot convert files. Therefore, interfaces can only be defined according to the principles of system B.

As in the above example, sometimes the system B is required to maintain a set of corresponding rules due to various pressures, and then the system B is responsible for interface encoding conversion. In essence, the workload of code conversion is not large, but it is troublesome to maintain a set of corresponding rules, and it is a long-term workload. If this set of rules is unnecessary for system B's business, system B must resolutely oppose such improper requirements.

2. Sequential Number and encoding. Who is the real primary key?

In enterprise-level data, almost all archives are encoded, such as personnel code, company code, and department code... These encodings all have a unique feature, that is, they are not repeated. There should be no two employees who share the code of one person. If there is a task that transmits personnel data from the other system through the database, can we use personnel encoding as the primary key of the intermediate table?

Of course not. Consider the simplest case: If the personnel code A sends an intermediate table, and then A leaves the company, another person is hired, and the original code A is enabled, then at this time, the Personnel code A (actually another person) needs to be sent to the intermediate table again, which will undermine the primary key uniqueness principle.

Now let's look at two concepts. I call it system-level primary keys and business-level primary keys. The so-called business-level primary keys can be understood as personnel encoding and can be seen in the front-end interface of the software, it is editable and unique. Its lifecycle is related to the business. For example, after Employee A leaves the company, the business-level primary key A disappears. When the next employee starts encoding A, the business-level primary key A is magically revived.

System-level primary keys can be understood as sequential numbers. They cannot be viewed on the Software Front-End Interface and cannot be edited by users. They are created by rules at the underlying level of the system. This rule has nothing to do with the business logic. The life cycle is the same as the life cycle of the data in the database. In short, only delete deletes the primary key, and no manual intervention is required.

When it comes to the serial number, another similar one is the batch number. It mainly appears in the db mode interface. When you need to perform one database transaction operation on multiple data sets. The batch number has some features of the system-level primary key and some features of the business-level primary key (for example, sometimes visible to the foreground ).

3. What is more and more common is not a function, but a table?

It is often seen that data that needs to be transmitted from multiple types is saved to a database intermediate table through various potential rules. Or, you can integrate data from the primary table, subtable, or relational table into a table or a field, and use various hidden rules to separate the tags. This is a stark violation of the "relational" database principle.

When designing interfaces, if we can make functional design more generic, the fewer interface tables, the higher the level of the atmosphere. Therefore, the datatype field is widely used and the custom definition def is favored. If a database's intermediate table is defined without having to set aside more than a dozen custom items, it will feel like an accident soon.

I have two points: 1st. It is very simple to add fields to the database. If you use a custom item, it is troublesome to add and maintain a specific service function description for the custom item. 2nd. Putting multiple types of data in a table is distinguished by datatype, which is very, very complex. As a result, the meaning of some fields is not single, and the data redundancy is high, the database index cannot thoroughly analyze the rules of the field content, and the efficiency is reduced.

The specific interface is determined by the business needs, rather than the creation of several database tables. Creating less tables does not save the development code workload, but increases the workload. For example, field A is mandatory in Data Type 1, but is not input in Data Type 2. If two data types are mixed into a table, when receiving data, the program verifies the required nature of the field. If two database tables are separated, field A of Data Type 1 can be set to database constraints, and the program does not require verification. Field A of Data Type 2 does not need to be defined at all.

4. Fear of data loss!

After the interface system goes online, if a piece of data is lost, it may cause the sender and receiver to shirk their responsibilities, and there is no good way to find evidence. As a result, backing up the data in the intermediate table becomes a habit, or accidentally using the backup table as a process for interface transmission, directly designing a State synchronization table.

Let's talk about the backup table first. Never make the backup table a synchronization table. Simply put, in the backup table writing program, only insert is allowed, and no select update delete is allowed. This is like backing up a table. Do not write all log files? No program will read the edit delete log file.

If you accidentally use select update delete when writing the backup table program, you will make the backup table a State synchronization table, that is, the status of the data in this table is coupled with the status of the entire interface program and final data, and with subsequent program processes. Never do this.

The following is a solution provided by the author to avoid data loss disputes:

The first is the role involved in the interface project, including the sender, receiver, and client (third party ). The customer puts forward the requirement, the receiver defines the intermediate table, and the sender works together. For example, the intermediate table t

Serial number field	Data Field	Status field
Id	Data	Status

1. Create the database intermediate table t in the customer database, neither the sender database nor the receiver database.

2. Create user_send in the client database, and grant the query and insert permissions of the intermediate table t to the user. Grant select, insert on t to user_send. Provide the user_send user to the sender, so that the sender can only query and write data to the intermediate table, and cannot delete or change the data.

3. Create user_receive in the client database, and grant the query and Status field update permissions of the intermediate table t to the user. Grant select, update (status) on t to user_receive. Provide the user _ receive user to the receiver. In this way, the receiver can only query data and only modify the status field. It cannot modify other fields or insert or delete data.

According to the above scheme, the database permission configuration has completely separated the responsible permissions of the sender and receiver, and there is no need to worry about data loss or tampering.

5. Preference for null values

The null value of a database is a very special type, and there is no uniform regulation on how to handle the null value, and there is no uniform regulation on the SQL language. This is a very dangerous message, because null values may accidentally cause bugs.

For example, for a varchar2 field in oracle, the query "select * from table where column ='' "is invalid and should be written as select * from table where column is null. For another database, the writing method is different.

Therefore, when designing database interfaces, make sure that fields have values.

I often see this design. For field A, the value 1 indicates ***, and the value 2 indicates xxx. If the value is null, it indicates $. This is a bad design.

Even if SQL and relational databases have standardized null values, they should not define null values in the fields of the intermediate table. This is equivalent to assigning a null value to the service. In addition, fields with null values are allowed, and database non-null constraints cannot be configured. It may also be caused by a sender program bug that the field is not written but is a valid input.

6. ABCD or 1234

Defining the status values often bothers us. We already have enough status fields and Status values. Some Status field values are ABCD... Some values are 1234... After a long time, we cannot clearly remember the meaning of the field and value.

Is it really necessary to have so many states? Think about it carefully. Except for the common "Initial init", "Successful success", "failed fail", and "Exception error ", I don't know which status can be clearly distinguished by English words. So why not look up the dictionary and use simple and clear English words to describe the status value.

7. It is not a reporter but a wise man to fear that the world will not be chaotic.

If you know what the problem is, don't say it out. If something goes wrong, let's say it out.