How to get a good name for tables and columns in the database?

In fact, when creating an object in the database, the Administrator must also name it. Some experienced database administrators will also be careful when naming them. It must be loud and representative of specific application scenarios. Sometimes naming a table or other database objects can be more difficult than maintaining a database. I have a deep feeling about this. When designing a database, a set of naming rules is compiled, and the head will feel tired. However, after you have compiled a few more sets, you will master the rules. It is much easier to get started with the rules and then name them again. Sometimes you can even use it as a messenger. This time I will share with you some rules on naming database objects. For the sake of simplicity, I will take the database object table as an example to talk about some naming techniques.

1. Remember the namespace

In Oracle databases, unlike other databases, there is a concept called namespace. In the same namespace, the names cannot be repeated. For example, tables and views share the same namespace. Therefore, not only cannot the table name be the same, but also the table name and view name cannot be the same. Because they are in the same namespace. Similarly, tables and functions are in the same tablespace, so they cannot have the same name. However, tables and indexes, tables, and constraints are different namespaces. That is to say, the table name can be the same as the constraint name. Therefore, when naming tables and other objects, the database administrator must know which objects share the same namespace. If the objects in the same namespace are different (such as views and tables), they still cannot get the same name.

To avoid duplicate names in the same namespace, we recommend that you add the inherent prefix of the object based on the object. For example, most database administrators generally do not add the table object prefix before the table name when naming a table. However, when defining a function or view object, the prefix is added. For example, the FN prefix may be added before the function, and the vi prefix may be added before the view. In this case, you do not have to worry about object name duplication in the same namespace. However, the concept of this namespace must be kept in mind by the database administrator. Even in practice, you can easily avoid this trap by prefix and other means. However, this namespace is also a necessary knowledge point in the Oracle Database Administrator certification exam. Therefore, the namespace administrator must have a clear understanding of the actual work and certification requirements.

Ii. Table name Case sensitivity control

Generally, table names or column names in Oracle databases are case-insensitive. When creating a table or column, even if the administrator uses a lower-case name, the database converts it to an upper-case name before saving it to the data dictionary, save them to the data dictionary. That is why the table name is changed to uppercase when the table name is viewed next time.

Although database objects such as tables and columns in Oracle databases are not case sensitive, if the database administrator does need to make the names of tables in the database system case sensitive, this can also be done. Generally, if the name is enclosed in double quotation marks, the name is case-sensitive in the Oracle Data dictionary. However, I would like to remind the database administrators that, although technically, the database system can be forced to use case sensitivity, in actual work, this is not recommended for most database administrators. If the case is mixed, you need to be careful when referencing these tables or column names. Even if the user or database administrator has an unforgettable skill, it is difficult to accurately remember the size of these names when writing songs. If the database administrator is hard to do this, it is likely to be self-seeking troubles. It is a headache to strictly distinguish the case from the case when querying or other jobs. Therefore, we recommend that the database administrator exercise caution when controlling the case sensitivity. Unless for good reason, do not use this double quotation mark to control the case sensitivity.

This double quotation mark can be used not only to control the case sensitivity, but also to reference some special characters. For example, when creating a table, you need to set a field with the name and number. Some database administrators prefer a name similar to num. This does not violate database naming rules. However, it will be troublesome to process. If you use the create statement to create a table, you must add double quotation marks to the field name. Otherwise, when you execute this statement, the database rejects the execution and prompts the user with an error message. Similar special symbols include a $ dollar symbol. When creating a table, they must use double quotation marks in the statement. However, after the fields are created, you do not need to use double quotation marks when referencing these objects. Likewise, although the Oracle Database supports these special symbols, the database administrator is not encouraged to use these special symbols in the naming of tables or columns. This may cause unnecessary trouble for subsequent references.

3. Set close relationships between tables, indexes, constraints, and columns

When creating a table, you can add indexes and constraints to some columns in the table. For example, in the employee information table, the uniqueness constraint of employee numbers is set. When creating a constraint, you also need to name the constraint. Although the constraints do not belong to the same namespace as tables and columns, there are basically no restrictions on naming. However, for the convenience of subsequent use, I have a small suggestion on naming constraints. Simply put, it is a good practice to give a table name to other objects directly related to the table. For example, there is a user table named ad_user (the object name is usually not added before the table name, but the module prefix can be added according to the module design of the application software ), A field in this table is called vlaue, which is used to store employee numbers. When designing a table, you must add an index to this field. The index name can be named IDX_USER_VALUE (that is, the index prefix, table name, and field name ). What are the benefits of doing so? First, we can ensure that the names of related objects are not repeated. Because the table name is not repeated, the table name and column name are combined to form the name of an object, so the chance of repetition is basically no. The second is to facilitate the Administrator to read, understand, and maintain. When you see the index or the name of the restricted object, you can see that this is the index or the field of the table where the constraint is used. You can also know whether this constraint is a uniqueness constraint or a check constraint; whether it is a primary key index or a foreign key index during indexing. Give the database administrator a clear impression. This provides convenience for subsequent maintenance, upgrade, adjustment, and reference.

Although database objects such as tables and columns in Oracle databases are not case sensitive, if the database administrator does need to make the names of tables in the database system case sensitive, this can also be done. Generally, if the name is enclosed in double quotation marks, the name is case-sensitive in the Oracle Data dictionary. However, I would like to remind the database administrators that, although technically, the database system can be forced to use case sensitivity, in actual work, this is not recommended for most database administrators. If the case is mixed, you need to be careful when referencing these tables or column names. Even if the user or database administrator has an unforgettable skill, it is difficult to accurately remember the size of these names when writing songs. If the database administrator is hard to do this, it is likely to be self-seeking troubles. It is a headache to strictly distinguish the case from the case when querying or other jobs. Therefore, we recommend that the database administrator exercise caution when controlling the case sensitivity. Unless for good reason, do not use this double quotation mark to control the case sensitivity.

This double quotation mark can be used not only to control the case sensitivity, but also to reference some special characters. For example, when creating a table, you need to set a field with the name and number. Some database administrators prefer a name similar to num. This does not violate database naming rules. However, it will be troublesome to process. If you use the create statement to create a table, you must add double quotation marks to the field name. Otherwise, when you execute this statement, the database rejects the execution and prompts the user with an error message. Similar special symbols include a $ dollar symbol. When creating a table, they must use double quotation marks in the statement. However, after the fields are created, you do not need to use double quotation marks when referencing these objects. Likewise, although the Oracle Database supports these special symbols, the database administrator is not encouraged to use these special symbols in the naming of tables or columns. This may cause unnecessary trouble for subsequent references.

3. Set close relationships between tables, indexes, constraints, and columns

When creating a table, you can add indexes and constraints to some columns in the table. For example, in the employee information table, the uniqueness constraint of employee numbers is set. When creating a constraint, you also need to name the constraint. Although the constraints do not belong to the same namespace as tables and columns, there are basically no restrictions on naming. However, for the convenience of subsequent use, I have a small suggestion on naming constraints. Simply put, it is a good practice to give a table name to other objects directly related to the table. For example, there is a user table named ad_user (the object name is usually not added before the table name, but the module prefix can be added according to the module design of the application software ), A field in this table is called vlaue, which is used to store employee numbers. When designing a table, you must add an index to this field. The index name can be named IDX_USER_VALUE (that is, the index prefix, table name, and field name ). What are the benefits of doing so? First, we can ensure that the names of related objects are not repeated. Because the table name is not repeated, the table name and column name are combined to form the name of an object, so the chance of repetition is basically no. The second is to facilitate the Administrator to read, understand, and maintain. When you see the index or the name of the restricted object, you can see that this is the index or the field of the table where the constraint is used. You can also know whether this constraint is a uniqueness constraint or a check constraint; whether it is a primary key index or a foreign key index during indexing. Give the database administrator a clear impression. This provides convenience for subsequent maintenance, upgrade, adjustment, and reference.

• Create tables and indexes in Oracle
• Implementation of oracle re-Indexing
• Complete Oracle File System Parsing
• ORACLE system table and data dictionary View
• In-depth analysis of Oracle ERP system module