Relational Database Design for developers

Directory database design is the cornerstone of a successful software project. Many practitioners believe that database design is not that important. In reality, the scenarios are quite the same. The number of developers is several times that of database designers. Most people use a part of the database, so it is easy to design the database. In fact, database design is also a subject

Directory database design is the cornerstone of a successful software project. Many practitioners believe that database design is not that important. In reality, the scenarios are quite the same. The number of developers is several times that of database designers. Most people use a part of the database, so it is easy to design the database. In fact, database design is also a subject

Directory

Database Design is the cornerstone of a successful software project. Many practitioners believe that database design is not that important. In reality, the scenarios are quite the same. The number of developers is several times that of database designers. Most people use a part of the database, so it is easy to design the database. In fact, database design is also a learning topic.

From the perspective of my experience, I prefer to design databases by developers in the early stage of the project (DBA is required for later optimization ). Based on the author's project experience, a developer proficient in OOP and ORM often designs more rational databases and better adapts to changes in requirements. If the reason is pursued, I guess it is because of database standardization, similar to some OO ideas (such as cohesion ). While DBA, the database designed has the advantage of being able to make full use of DBMS capabilities and use SQL and DBMS to implement logic implemented by many programs. Compared with developers, the database optimized by DBA is more efficient and stable. As shown in the title, this article aims to share the database design experience of a developer, which does not involve complex SQL statements or DBMS usage, so it is not limited to a DBMS product. I really hope this article will be helpful to developers and help readers find and fix the gaps.

The RDBMS12 rule of Codd-the origin of RDBMS

Edgar Frank Codd is known as "the father of relational databases" and won the Turing Award in 1981 for his outstanding contributions to the theory and practice of database management systems. In 1985, Dr. Codd released 12 rules that clearly define the concept of a relational database as a guiding principle for the design of all relational database systems.

Design Stage

(1) planning stage

The main task in the planning stage is to analyze the necessity and feasibility of the database. Determine whether to use the database, which type of database to use, and which database product to use.

(2) concept stage

The main task in the concept phase is to collect and analyze requirements. The main requirement is to identify data entities and business rules. For a system, the database mainly includes business data and non-business data. The definition of business data depends on the analysis of user requirements at this stage. Identify business entities and business rules as much as possible, have a preliminary understanding of the system as a whole, and understand the data flow process. Theoretically, this phase will reference or produce a variety of documents, such as the "use case diagram", "Data Flow Diagram", and other project documents. If these results can be produced at this stage, it will undoubtedly be of great help to the later stage. Of course, many documents are beyond the consideration of database designers. In addition, if you are not proficient in this field and the user's business, please give up your idea of completing the user demand analysis independently. The user is not a technical expert. If you cannot assume the role of "business consultant", please select to cooperate with the relevant personnel of the project team or report the user to PM as a risk. Again, in most cases, users are only industry practitioners, rather than professional technicians. We only collect requirements from users rather than relying on user knowledge.

Some tips can be used to record user requirements. Of course, this part may be beyond the responsibilities of the Database Designer:

In addition, business rules must be strictly handled and detailed records must be recorded. In subsequent stages, the design will be based on these business rules.

When this stage ends, you should be able to answer the following questions:

And get the following information:

(3) logic phase

The main work of the logic phase is to draw a E-R diagram, or modeling. There are many modeling tools with different graphical representation methods and software. The use of these tools and software is not critical, and I do not recommend that you spend a lot of time on the choice of modeling methods. For most applications, a E-R diagram is sufficient to describe the relationship between entities. The key to modeling is ideas rather than tools. Software only plays an auxiliary role. Identifying entity relationships is the focus of this phase.

In addition to the entity relationship, we should also consider the attribute fields (value type, range, constraints)

(4) implementation stage

The implementation phase mainly defines the table corresponding to the E-R graph for the selected RDBMS, considering the attribute type, scope and constraints.

(5) Physical stage

The physical stage is a stage of verification and optimization. It is to deploy a database on the actual physical device for testing and tuning.

3. Design Principles

(1) reducing dependency on database functions

Functions should be implemented by programs rather than DB. The reason is that if the function is implemented by DB, once the replaced DBMS is not as powerful as the previous system and cannot implement some functions, we will have to modify the code. Therefore, in order to prevent such cases, the function should be implemented by a program, and the database is only responsible for data storage to achieve the lowest coupling.

(2) principles for defining Object Relationships

When defining the relationship between an object and other entities, consider the following:

Relationship and table quantity

(3) columns indicate unique values.

If it represents the coordinate (0, 0), two columns should be used instead of "0, 0" in one column.

(4) column order

Column order is irrelevant to the table, but in terms of habits, sorting columns in the order of "primary key + foreign key + Entity Data + non-Entity Data" is obviously readable.

(5) define primary keys and Foreign keys

The data table must define the primary key and foreign key (if there is a foreign key ). Defining primary keys and Foreign keys is not only a requirement of RDBMS, but also a requirement of development. Almost all code Generators need this information to generate code for common methods (including SQL and references). Therefore, it is necessary to define the primary key and foreign key in the development phase. It is necessary in the development stage because many teams will conduct a large number of tests for performance and ensure that there will be no major defects in the integrity of references, all the foreign keys of the database are deleted to achieve optimal performance. I believe that foreign keys and Hong Kong servers should be retained when there is no performance problem, and even if the performance is really faulty, the SQL text should be optimized, rather than giving up foreign key constraints.

(6) Selection key

1. Manual and natural keys

Artificial health-the unnatural attributes of an object, which are imposed by people as needed, such as GUID, have no significance to the object; natural health-the natural attributes of the object, such as ID card numbers.

Benefits of manual keys:

The key value remains unchanged.

Always Single-Column Storage

Disadvantages of manual keys:

Because the manual key is a unique value without practical significance, you cannot use the manual key to avoid repeated rows.

We recommend that you use the manual keys. The reason is as follows:

In the design phase, we cannot predict the values actually required by the code, so we simply discard the guess key and use the manual key.

The manual key is responsible for complex processing of Object Relationships, rather than any attribute descriptions. This design ensures that the entity relationship is highly decoupled from the object content, and the design philosophy is clearer.