Test System Database Design Experience

Since it takes us a whole month to build the basic database of the educational administration system, we also need to write a blog to summarize this month. First, let's take a look at the existing part of the educational administration system to facilitate the following content: the educational administration system includes four parts: Basic System, examination system, evaluation system, and course selection system, the basic system is used to maintain the basic data. Other systems can know the function only by looking at the system name.

Name

The first step in database design is how to name the database. The first naming rule is:

• T _ indicates the table type, and V _ indicates the view type.
• E _ indicates the object table, and R _ indicates the relational table.
• K _ indicates the examination system table, B _ indicates the basic system table, and C _ indicates the Course Selection System table.

As a result, the name of a table can make t_e_k_examstudent. At first, this naming method became proud: in the database, tables can be automatically sorted by naming rules. But the problem arises. In this way, a simple SQL statement will become:

SELECT * FROM T_E_K_ExamStudent

If you add a cross-Table query, how many _ s are required? Isn't it a sin for yourself? In addition, k is the first character in the examination pinyin, and B represents the first character in the English basic, Which is messy in Chinese and English. After modification, the final naming rule is:

• T indicates the table type, and V indicates the view type.
• The entity table has no prefix, r indicates the relational table, and h indicates the historical table.
• E indicates the examination system, a indicates the evaluation system, and C indicates the course selection system.

In this case, an SQL statement can be

SELECT * FROM TE_ExamStudent

Such statements are clear, easy to write, and easy to maintain.

Design

The overall design of the educational administration system is as follows:

Basic data is maintained by the basic system, such as student information, instructor information, and class information. It is responsible for addition, deletion, modification, and query of such information. Other systems have their own tables. for basic information, other tables only have the right to read and cannot be modified or deleted.

The table design in the educational administration system is as follows:

That is, entity tables cannot be directly associated. They are all associated through the third relational table. This is very different from the previous database design. In the past, only when there were many to many table data pairs, the third table was associated; but now only two tables are related, whether it is one-to-many or multiple-to-many, they are associated with the third table.

The benefit of this design is that when you no longer have a major in a school, you only need to delete the data in the relational table. Do not change either of the two physical tables, this fully guarantees the independence and scalability between tables, which is the most important factor in basic data.

Discussion

When I designed the database, the biggest difference was that the basic database table fields were rich or streamlined? For example, do the student table contain fields such as ID card number and email address?

One solution is concise. When additional fields need to be added, these fields are added to the newly created relational table. The other solution is rich type, all fields that can be used or temporarily unavailable should be added to the object table. Because the entity field exists in the relational table, the data redundancy is greater than that in the object table, so we have discussed the rich type.

Now let's think about why this discussion came into being. This is largely because the design of the database directly exceeded the logic design stage and ignored the conceptual design stage, resulting in a discussion of the entity attributes.

Supplement

At this stage, the design of the basic database of the educational administration system has been completed. Here, we need to add a few more points:

• Historical data processing, data cannot be deleted, but can be transferred to the historical table for future statistics query.
• Subsystem tables and menus. Each system is an independent subsystem. Therefore, a table is required to record the name, logo, release address, release time, availability, and other fields of each system, ensure that subsystems can be dynamically loaded on the interface.
• Record each operation and every operation of the system, such as logon, adding records, modification, and deletion, should record the time, permission, user, machine, and other information.
• Operations on each object can be added, deleted, modified, and queried as long as they have sufficient permissions. At the same time, because users cannot modify databases and can only indirectly modify data through the system, the addition, deletion, modification, and query operations on each object must be reflected on the interface.

The database of the educational administration system is summarized here. This design is deeply touched by the fact that it is not as important to believe in books as it is to have no books, but not to tie yourself to it, flexible use according to local conditions can be used in a practical manner.