1. Naming Conventions

Adopting unified naming conventions for database objects is of great significance, as it brings a series of advantages and conveniences to database development, maintenance, management, and optimization:

1. Improved Readability: Unified naming conventions make the names of database objects (such as tables, fields, indexes, views, stored procedures, etc.) more intuitive and easy to understand. This helps developers and database administrators quickly grasp the purpose and function of each object.

2. Enhanced Maintainability: When database structures become complex, unified naming conventions can reduce maintenance costs. Clear and consistent naming helps to quickly locate problems and make necessary modifications, thereby improving maintenance efficiency.

3. Promoted Team Collaboration: In team development environments, unified naming conventions ensure that all team members have a common understanding of database objects, which helps improve communication efficiency and reduce misunderstandings and errors.

4. Facilitated Data Migration and Integration: Standardized naming conventions help migrate data between different systems and platforms, and also facilitate integration with other systems, as other systems can understand the database structure through these standardized names.

5. Reduced Errors and Conflicts: Unified naming conventions can reduce the possibility of naming conflicts and spelling errors, thereby reducing erroneous operations caused by misunderstanding names.

In conclusion, unified naming conventions for database objects are one of the best practices in database management. They help improve the overall quality and efficiency of databases while bringing long-term benefits to organizations.

info

PawSQL's SQL audit system provides 10 review rules for naming database objects. Users can customize configurations based on these rules, which can meet the checking requirements for naming conventions in most scenarios across various databases.

RuleCode: NC001

Name: Table Naming Convention

Rule Description: This rule defines the naming convention for tables, typically requiring table names to use lowercase letters and underscores to maintain consistency and readability.

Warning Level: Notice

Review Object: CREATE TABLE

Trigger Condition: Table name does not comply with the naming convention (e.g., uses uppercase letters or special characters).

Configurable: Yes

Example: The expression can be configured as a regular expression, such as ^[a-z]+(_[a-z]+)*$, indicating that table names should be all lowercase and consist of letters and underscores.

Supported Database Types: All (ALL)

RuleCode: NC002

Name: Column Naming Convention

Rule Description: This rule defines the naming convention for columns, typically requiring column names to use lowercase letters and underscores to maintain consistency and readability.

Warning Level: Notice

Review Object: CREATE TABLE, ALTER TABLE ADD COLUMN

Trigger Condition: Column name does not comply with the naming convention.

Configurable: Yes

Example: The expression can be configured as a regular expression, such as ^[a-z]+(_[a-z]+)*$, indicating that column names should be all lowercase and consist of letters and underscores.

Supported Database Types: All (ALL)

RuleCode: NC003

Name: Unique Key Naming Convention

Rule Description: This rule defines the naming convention for unique keys, typically including a combination of table name and column name for easy identification and management.

Warning Level: Notice

Review Object: CREATE INDEX

Trigger Condition: Unique key name does not comply with the naming convention.

Configurable: Yes

Example: The expression can be configured as ^uk_{{table}}_{{column_list}}$, where {{table}} and {{column_list}} are variables, indicating that the index name should start with uk_ followed by the table name and column list.

Supported Database Types: All (ALL)

RuleCode: NC004

Name: Primary Key Naming Convention

Rule Description: This rule defines the naming convention for primary keys, typically including a combination of table name and pk for easy identification of primary key constraints.

Warning Level: Notice

Review Object: CREATE TABLE, ALTER TABLE ADD CONSTRAINT

Trigger Condition: Primary key name does not comply with the naming convention.

Configurable: Yes

Example: The expression can be configured as ^pk_{{table}}_{{column_list}}$, where {{table}} and {{column_list}} are variables, indicating that the primary key name should start with pk_ followed by the table name and column list.

Supported Database Types: All (ALL)

RuleCode: NC005

Name: Foreign Key Naming Convention

Rule Description: This rule defines the naming convention for foreign keys, typically including a combination of referencing table name, referencing column name, referenced table name, and referenced column name.

Warning Level: Notice

Review Object: CREATE TABLE, ALTER TABLE ADD CONSTRAINT

Trigger Condition: Foreign key name does not comply with the naming convention.

Configurable: Yes

Example: The expression can be configured as ^fk_{{referencing_table}}_{{referencing_column}}_{{referenced_table}}_{{referenced_column}}$, where {{referencing_table}}, {{referencing_column}}, {{referenced_table}}, and {{referenced_column}} are variables, indicating that the foreign key name should include the names of referencing and referenced tables and columns.

Supported Database Types: All (ALL)

RuleCode: NC006

Name: Index Naming Convention

Rule Description: This rule defines the naming convention for indexes, typically including a combination of table name and column name for easy identification and management of indexes.

Warning Level: Notice

Review Object: CREATE INDEX

Trigger Condition: Index name does not comply with the naming convention.

Configurable: Yes

Example: The expression can be configured as ^idx_{{table}}_{{column_list}}$, where {{table}} and {{column_list}} are variables, indicating that the index name should start with idx_ followed by the table name and column list.

Supported Database Types: All (ALL)

RuleCode: NC007

Name: Auto-increment Column Naming Convention

Rule Description: This rule defines the naming convention for auto-increment columns, typically requiring a specific naming pattern such as id for easy identification of auto-increment columns.

Warning Level: Notice

Review Object: CREATE TABLE, ALTER TABLE ADD COLUMN

Trigger Condition: Auto-increment column name does not comply with the naming convention.

Configurable: Yes

Example: The expression can be configured as id or ^id$, indicating that the auto-increment column should be named id.

Supported Database Types: All (ALL)

RuleCode: NC008

Name: Database Naming Convention

Rule Description: This rule defines the naming convention for databases, typically requiring database names to use lowercase letters and hyphens (kebab-case) to maintain consistency and readability.

Warning Level: Notice

Review Object: CREATE DATABASE

Trigger Condition: Database name does not comply with the naming convention.

Configurable: Yes

Example: The expression can be configured as ^[a-z]+(-[a-z]+)*$, indicating that database names should be all lowercase and consist of letters and hyphens.

Supported Database Types: All (ALL)

RuleCode: NC009

Name: Schema Naming Convention

Rule Description: This rule defines the naming convention for database schemas, similar to the database naming convention, requiring schema names to use lowercase letters and hyphens.

Warning Level: Notice

Review Object: CREATE SCHEMA

Trigger Condition: Schema name does not comply with the naming convention.

Configurable: Yes

Example: The expression can be configured as ^[a-z]+(-[a-z]+)*$, indicating that schema names should be all lowercase and consist of letters and hyphens.

Supported Database Types: All (ALL)

RuleCode: NC010

Name: CREATE TABLE AS SELECT Naming Convention

Rule Description: This rule defines the naming convention for tables created using the CREATE TABLE AS SELECT statement, typically requiring the new table name to have a specific format.

Warning Level: Notice

Review Object: CREATE TABLE AS SELECT

Trigger Condition: Table name created using CREATE TABLE AS SELECT does not comply with the naming convention.

Configurable: Yes

Example: The expression can be configured as ^{table}_TS$, where {table} is a variable, indicating that the new table name should be based on the selected table name and end with _TS.

Supported Database Types: All (ALL)