Considerations for Oracle database table design

Table is one of the most basic objects in an Oracle database. The base object for a database is very important for a high-rise building from the ground up. Because the design is reasonable, directly related to the performance of the database. From Oracle database rookie to database expert this process, in table design and management, more or less, will make some mistakes. I would like to talk about my experience and lessons in this area today, perhaps to give you some warning role.

Table is one of the most basic objects in an Oracle database. The base object for a database is very important for a high-rise building from the ground up. Because the design is reasonable, directly related to the performance of the database. From Oracle database rookie to database expert this process, in table design and management, more or less, will make some mistakes. I would like to talk about my experience and lessons in this area today, perhaps to give you some warning role.

Experience one: When designing large databases, place the nullable columns behind the table.

During database table design, some field requirements must be non-empty, such as the table's keywords, document number fields, and so on. When database tables are created, these fields often need to be set to non-empty. In this case, you can force the user to enter data to enhance the consistency of the data.

Also, in a table, there will be a number of fields, such as the Employee Information table, employee preferences, comments and other fields, may be allowed to be empty. In database jargon, these fields with no content are "NULL" fields.

When designing a database table, pay special attention to this type of empty field. On the one hand, this null field does not mean that it stores spaces. The use of the "" "symbol to query the empty field records, often do not find the results we want. Because if the field is null when the database is stored, this value is not stored at all. On the other hand, because of the above characteristics, so, in the design of the table, it is best to put the null field at the end of the table, when the database is larger or empty fields are more, you can greatly reduce the storage space of the database.

In addition, when the database is created, it is sorted by the time the column was established. Therefore, when a column is established, the order of the columns cannot be adjusted unless it is re-established again. This requires that the database administrator should have a clear definition of the column, and whether it is empty before creating the table. Only in this way can the order of the columns be reasonably arranged.

Experience Two: Develop the habit of writing notes diligently.

Judging whether a person is a database expert can be seen from a small habit of "commenting". If a person in the database development, diligent and write comments, such as in the establishment of tables or fields, the ability to use the comment command to annotate the table and field, you can be judged to some extent he is the expert. On the contrary, if the database is developed, the comment is not much, it can be judged basically, he is a novice beginner. Or, it didn't participate in the design of a large database.

This is mainly because, in the database development process, often is not a person can complete. When developing an ERP database, there may be people who are specifically responsible for developing the underlying tables, while others are responsible for maintaining the views, and so on. Everyone is a division of labor. At the same time, there are database tables and fields that programmers need to call back in the foreground. Therefore, the large and medium-sized database and application systems are often the result of collaboration.

To this end, in order to improve the readability of tables and fields, a database manager developed a table, everyone can read, you need to set the table and field more detailed comments. In an Oracle database, in order to provide meaningful help to other partners, you can use the comment command to describe the table, the role of the field, and some reference considerations. This small command can greatly improve the readability of tables and fields, and improve the collaboration between the database and the program development team.

Experience Three: Choose the right data type and length.

Although the database system processes data, some data types are compatible, or automatic conversions are made to the associated data types. For some date-type fields, they can also be assigned to fields of the character type, and integer-type fields are compatible with floating-point data types. However, database designers can not because of the characteristics of the database system, to relax the data type checks. Because sometimes, such as in the basic data import or data batch update, although the end can successfully complete the task. However, in this process, the database needs to be transformed into data types, which adds to the additional burden on the database.

In addition, the length of the field needs to be considered when designing the database. If the field is designed too long when the table is created, the storage space is wasted. Conversely, if the design is too short, it will affect the use of the foreground system. Therefore, the database administrator for the length of the field, but also to attract sufficient attention.

Furthermore, if you cannot confirm the length of a field when creating a field, it is best to take a variable length data type. In the case of character data types, there are two types of char and VARHAR2. where the char character type is primarily used to store fixed-length strings, with a maximum allowable length of 2000 bytes. If the actual stored information does not have a defined number of bits, the system adds a space to the column value until its length is reached. It can be seen that with fixed-length field types, even if the data is not so much, it takes up so much storage space. Therefore, in this way, the waste of storage space will be relatively large. The latter is primarily used to store variable-length character data. When the data is inserted in the VARCHAR2 column, the database does not add a space at the end if it does not have a maximum number of digits. This allows for maximum space utilization.

So, at the time of table design, the database administrator chooses the appropriate data type and sets the appropriate length for it.

Experience Four: Determine the integrity constraints and default values that the table needs to take.

Some database administrators like to design side-by-side development when designing a database. For example, when designing a table, it is not certain that the fields are to be constrained at first, which fields cannot be empty, which fields require any default values, and so on. Wait until you need it, and then add it at random. This way of doing things, will often bring instability to the database of hidden dangers.

If a database administrator creates a table, it does not initially set a field to the default value. Later, the foreground program developer said that a default value needed to be added to this field. However, the table now has the underlying data related to the system. When a table with data is available, it is often not allowed to reset the default values again. Otherwise, the database will have a certain negative impact.

Therefore, the author believes that the database administrator needs to determine the integrity of the table before establishing the table. such as which columns are the primary key, which columns are foreign keys, which columns need uniqueness, and so on.

Experience Five: Determine the type of table that needs to be used.

In the Oralce database, stored user data can use standard tables, index tables, tables, and partitioned tables. Sometimes, based on the use of unused applications, choose different types of data tables, often can greatly improve the performance of the database, especially the efficiency of data query.

As appropriate, database administrators can use the built-in tables to conserve storage space and improve the performance of specific types of SQL statements. However, we usually use more out of the basic table, is the index table. Because the index table in some special applications, can show good performance. As in the index table, you can increase the query speed of the frequently used columns through the overflow storage function, if you often need to query the whole record through the primary key, then using the Index table can improve the query efficiency obviously. Of course, the index table can not be used indiscriminately. If the data stored in the Index table changes more frequently, then the index table of this type of table, but will be more inefficient.

Therefore, selecting the appropriate type for the database table can greatly improve the performance of the database. The greater the number of records in a database, the more obvious this advantage is.

In a word, the author thinks that, in the design of Oracle database, the establishment of table is a basic work. However, there are times when the performance of a database is more far-reaching than other content. Therefore, we should pay attention to the design of the table, starting from the foundation, in order to improve the performance of the database efforts.

Considerations for Oracle database table design