Database needs to be designed, and database design is reflected in two aspects:
Database logic design: Design the logical structure of the database, which has nothing to do with the specific DBMS, and mainly reflects business logic.
Database physical design: Design the physical structure of the database, select RDBMS (such as Oracle, Sybase, etc.) according to the logical structure of the database, and design and implement the storage structure and access methods of the database.
The logical design of the database determines the overall performance of the database and its applications, and the tuning position. If the database logic design is not good, all tuning methods have limited effects on improving database performance. In order to complete the database design method, the normalization theory of the database must be abided by. Standardization theory provides theoretical guidance and tools for database logic design, reduces data redundancy while saving storage space, and speeds up addition, deletion, and modification.
In addition, when designing the standard database logic, you should also consider appropriately breaking the normative rules, that is, the denormalization design, to reduce the number of indexes and tables, reduce the number of connection operations, and thus speed up the query. Commonly used anti-standard techniques include adding redundant columns, adding derived columns, regrouping tables, and so on.
Add redundant columns: Sometimes the columns to be queried are distributed in different tables. If the frequency of this connection query is relatively high, you can add this column from other tables as needed. So that multiple tables have the same column, it is often used to avoid join operations when querying. But its disadvantage is that it requires more disk space, and at the same time it needs to increase the workload of maintaining the table due to integrity issues.
In short, when designing the database logic, we must choose the database mode reasonably according to the application environment and the specific conditions of the real world.
Because the database logic design does not involve specific environments, there is no need to consider platform and product issues. But in the database physical design, it is closely related to UNIX and database systems. Please refer to the relevant content in the two technical monographs "Oracle System Construction, Operation and Maintenance, Clustering, Disaster Recovery and Performance on AIX" or "Sybase Database Implementation and Management on UNIX and Windows". The book was edited by the famous scholar Wen Ping and has a strong guiding significance.