Considerations for Oracle Database Table Design

A table is one of the most basic objects in an Oracle database. From the ground up, the base object is very important for databases. Whether the design is reasonable is directly related to the database performance. From the Oracle database cainiao to database experts, table design and management are more or less prone to errors. Today, I will talk about my experiences and lessons in this area, which may serve as a warning.

Experience 1: when designing a large database, place the columns that allow NULL behind the table.

During database table design, some fields must be non-empty, such as table keywords and document number fields. When creating a database table, you must set these fields as non-empty. In this way, you can force the user to input data to enhance data consistency.

At the same time, there are also many fields in a table, such as employee information tables, employee hobbies, comments, and other fields, which may be left blank. In database terminology, the fields with no content are "NULL.

When designing database tables, pay special attention to empty fields of this type. On the one hand, this NULL field does not mean that it stores spaces. If you use the "'' "symbol to query records with empty fields, we often cannot find the desired results. Because when the database is stored, if the field is NULL, this value is not stored at all. On the other hand, because of the above features, when designing a table, it is best to put the field that allows NULL at the end of the table. When the database is large or has many blank fields, it can greatly reduce the storage space of the database.

In addition, when a database creates a table, it is sorted by the column creation time. Therefore, after a column is created, the column sequence cannot be adjusted unless it is deleted again. This requires the database administrator to define the column and whether it is empty before creating a table. Only in this way can the column order be reasonably arranged.

Experience 2: develop the habit of writing comments frequently.

Judging whether a person is a database expert can be seen from the habit of "commenting. If a person is diligent in writing comments during database development, for example, when creating a table or field, he can use the comment command to annotate the table and field, it can be determined to some extent that he is an expert in this field. On the contrary, if there is not much comment in the developed database, you can basically judge that he is a beginner. Or, they have not participated in the design of large databases.

This is mainly because, in the database development process, it is often not done by one person. For example, when developing an ERP Database, some people may be responsible for developing basic tables, while others are responsible for maintaining views. We all work in a division of labor. At the same time, programmers also need to call the database tables and fields in the background. Therefore, large and medium-sized databases and application systems are often the result of collaboration.

To improve the readability of tables and fields, you can understand a table developed by a database administrator. Therefore, you need to set detailed annotations for tables and fields. In the Oracle database, to provide meaningful help information to other partners, you can use the Comment command to describe the role of tables and fields and some references. This small command can greatly improve the readability of tables and fields, and improve the collaboration between the database and the program development team.

1 2 3 Next Page