Table naming conventions

When working with databases, developers often create various tables to support different operations. This topic provides guidelines for naming tables in database operations to ensure consistent naming conventions.

Table naming

• Table naming conventions for MySQL-compatible mode and Oracle-compatible mode are as follows:

  • General guidelines for OceanBase Database MySQL-compatible mode:

    • Table names consist of lowercase letters, numbers, and underscores, start with a lowercase letter, and end with a lowercase letter or number.
    • The length is at least 3 characters (according to development standards) and less than the maximum allowed by the database.
    • Do not use keywords or reserved words from OceanBase Database MySQL-compatible mode as table names.

    Notice

    The maximum length of a table name in MySQL-compatible mode of OceanBase Database is 64 characters. The maximum length may vary with the specific version.

  • General guidelines for OceanBase Database Oracle-compatible mode:

    • Table names consist of uppercase letters, numbers, and underscores, start with an uppercase letter, and end with an uppercase letter or number.
    • The length is at least 3 characters (according to development standards) and less than the maximum allowed by the database.
    • Do not use keywords or reserved words from OceanBase Database Oracle-compatible mode as table names.

    Notice

    • Except for the letter case requirements, the naming conventions for OceanBase Database Oracle-compatible mode are the same as those for MySQL-compatible mode.
    • The maximum length of a table name in Oracle-compatible mode of OceanBase Database is 64 characters. The maximum length may vary with the specific version.

• Start table names with a letter.

• Use clear, self-explanatory names. For example: "TEST".

• Avoid beginning or ending table names with an underscore.

• Avoid starting table names with a number.

• Do not use system reserved words or keywords as table names.

• Avoid having only numbers between underscores in table names.

• Use singular nouns for table names.

• Begin table names with the subsystem name or a standard abbreviation, followed by the function name or another standard abbreviation, separated by an underscore. Ideally, use the format "business name_table purpose". For example: "ACCOUNT_USER".

• When adding numeric identifiers to table names, use sequential numbers starting from "00". For example: "ACCOUNT_USER_00", "ACCOUNT_USER_01", "ACCOUNT_USER_02".

• For tables partitioned by time, use the format "base table name_time", with the time as a four- to six-digit abbreviation representing the data’s date. For example, the partitioned table for "ACCOUNT_USER" in January 2022 would be named "ACCOUNT_USER_2201".

• For intermediate tables used to store temporary results, use the format "tmp_table name (or abbreviation)_column name (or abbreviation)_creation date". For example: "tmp_account_tbluser_20220224".

• For backup tables or tables that capture snapshots of source tables, use the format "bak_table name (or abbreviation)_column name (or abbreviation)_creation date". For example: "bak_account_tbluser_20220224".