Introduction to Database Design

As a programmer, the database design before programming is very important, which will be directly related to the subsequent coding work. Here we will talk about some details about the database design process.

1. Select fields (ID, code, number) for the primary key of the data table)

ID (ID) is generally a guid. A string in this format is a globally unique string. When the program needs to call databases of the same structure on different platforms, it is recommended to use guid as the primary key. The advantage of doing so is that when the data of different platforms is summarized on a certain platform, the Data summary in the same table will not be able to be summarized normally because the primary key is the same. Code is generally a string of non-full numbers, such as a string in the mixed Letter format. Especially when designing an object model with multi-level associations. For example, when the object model of tree structure relationships, such as personnel department structure and multi-level product classification (see ).

For the Department structure shown in, set the encoding code of a department in the database, and add colored numbers to the structure. The advantage of doing so is that it is easy to obtain the Department Information Based on such encoding. For example, to obtain the information of all personnel at the level under Branch 3 (0103), you can take the first four digits of the department code in the table and determine whether the information is equal to 0103, in this way, we can obtain the codes of all the departments at the level under Branch 3, and then read the personnel information from the personnel information table. Number (serial number) is usually generated by the system or automatically generated by the program when the identification record requires continuous counting. For example, if there are multiple physical objects, you can use serial numbers to differentiate them. When there are multiple identical books in the library, you can use a serial number to differentiate them. At the same time, we can intuitively see the number of identical individual records of this object, provided that the data has not been deleted. You can select a database based on the actual situation to achieve twice the result with half the effort.

II. The idea of changing space for time

When a primary table and multiple slave tables have a primary-foreign key relationship, the common practice is to access the primary key information recorded in the primary table. In this way, we need to combine two or more tables to read and combine the data to obtain the required information. In the database ideology, it is mentioned that the database design should avoid data redundancy as much as possible, which can save the space occupied by data storage. However, in today's society. In the era of rapid development of hardware technology, the storage space of storage media can become larger and larger, and there is no need to consider the occupation of databases. Despite the rapid development of storage media, the processing speed of computers is hard to keep up. Therefore, when designing a database, consider how to make the operation respond quickly to save the user's waiting time. This is the idea of changing the space for time. For example, if the user name in the User table needs to be stored in the master table and the user table has a large amount of data, you can consider directly storing the user name in the master table rather than storing the primary key information recorded in the user information. In this way, only the primary table can be queried to obtain the information we need, without the need to join the user table for query. Of course, this is not a common practice. Pay attention to the following points. First, the fields involved in the slave table are basically not associated with other business processes, for example, I only need to display this name during the query, and do not need to query or modify it by mistake, and other business operations. In addition, this data needs to be viewed as a historical record, so we should store the information ontology as much as possible rather than the primary key of the records in the table where the information ontology is located. Otherwise, when you modify the table information and query the record again, you will find that it is inconsistent with the actual situation. For example, a company's courier is the company's front-end, but there will always be personnel changes at the front-end, people are not there, but the employee ID is still there, it will be handed over to new users. The sender's name is saved directly when the sender's information is recorded. Sometimes, we also choose to store the primary key and information body of the slave table directly in the master table. For example, save the user name and store the user's employee ID. In this way, you can obtain the information of the master table based on the employee ID and get the user's employee ID and name. Although data redundancy exists in this case, it is not a good solution to achieve query efficiency.

Finally, I will mention it. The database should be designed based on actual conditions, supplemented by theories, taking into account the execution efficiency. These methods may not be suitable for different situations. For more information, see.