Database Design Considerations

Before Database Design

Prior to database design, there should be clear database design specifications, including naming rules for tables, views, fields, design constraints, and encoding rules for stored procedures. At the same time, the database design should follow the Implementation ideas from the logical design to the physical design, and create a data dictionary and ER Model While fully understanding the customer's needs, comply with the basic requirements of the database design paradigm.

Design tables and fields

For the database design of complex business systems, first, you can get the corresponding database table from the erdiagram after transferring the logic model to the physical model, the names of tables and fields in the database can standardize and accurately express the meaning of data items. Table Design and division standards generally reach the third paradigm, and 3nf is also considered to have a good balance in performance, scalability, and database integrity. However, 3nf is not an absolute criterion. When there are sometimes performance considerations, we should make proper redundancy to improve performance.

For the Oracle ERP system, each data table has five fields by default: creator, creation time, Updater, Update Time, and validity. This is an important part for us to learn from. In addition, you must be careful when selecting the database design field name to conflict with the reserved words of the database system, such as type, name, and DESC, which may be important reserved words of the database.

The table contains a "delete tag" field to mark rows as deleted. Do not delete a single row in a relational database. It is best to clear the Data Program and carefully maintain the integrity of the index. In addition, when designing databases, table triggers are often used to ensure the integrity of some references. However, triggers often affect program debugging and are not easily noticed by others, therefore, you can use other methods to implement the trigger function. The capacity of table fields is also a problem to be considered during design. Generally, you need to leave a certain margin for the field length during design to facilitate later expansion.

Select keys and Indexes

When designing a database, you can often use some numbered fields with unique characteristics as the primary key of the data table. However, we recommend that you use the meaningless fields automatically generated or automatically generated by the database as the primary key of the data table. In this way, the integrity and efficiency of database indexes are well controlled, and when you have a consistent key structure, it is easy to find the logic trap.

The foreign key can ensure the reference constraints between data tables, but my personal experience is to control the reference integrity between data tables through the program area without the foreign key constraints.

Indexing is one of the most efficient ways to obtain data from a database. Index technology can be used to solve 95% of database performance problems. The system automatically sets up the clustered index for the primary key field. Because foreign key fields are often used in multi-table join queries, you must also consider creating related indexes. Indexes are generally required for fields with large data volumes and frequent searches. However, indexes are not suitable for creating too many indexes. One is space consumption, which increases the maintenance cost of tables and affects the performance of maintenance operations such as updating and inserting tables.

Ensure database integrity

Both the primary and foreign key constraints and triggers can effectively implement the database's reference integrity. However, my personal opinion is that it is best to implement complicated referential integrity in the program as a business rule. If there is a many-to-one relationship between two entities and it may be converted to many-to-many relationships, you 'd better set them to many-to-many relationships at the beginning. Switching from the existing multi-to-one relationship to the multi-to-many relationship is much harder than the first one.

It also plays an important role in maintaining the integrity of the reference. To provide another abstraction layer between your database and your application code, you can create a dedicated view for your application without requiring the application to directly access the data table. This gives you more freedom to handle database changes.