Best practices for database development-OceanBase

A table is a structure for organizing and storing data in a database. By designing tables in accordance with database development standards, you can streamline data management and processing, ensure data security and efficiency, and enhance the overall value of the data.

This topic describes the best practices for naming conventions and structure design.

Naming conventions

When you design database tables, make sure that the table naming conventions are clear and accurate. Appropriate naming conventions not only improve code reliability but also ensure structural unity and maintainability of data.

Table naming conventions

Avoid special characters: Table names must not contain special characters. We recommend that table names do not start or end with an underscore (_), or start with a digit.
No reserved words or keywords: Table names must not include reserved words or database keywords.
Avoid numeric-only names: Table names must not consist only of digits concatenated with underscores.
Use singular forms: Table names must be in singular form rather than plural.
Maintain consistent letter case: It is recommended to use a consistent letter case for table names.
Ensure semantic clarity: Table names must have clear and meaningful semantics that are easy to understand. For example, a test table can be named test.
Follow naming conventions: A table name must start with the subsystem name or a standard abbreviation, followed by the functionality description, separated by an underscore (_). For example, account_user.
Numeric identifiers: If a table name requires a numeric identifier, it is recommended to increment the identifier progressively starting from 00. For example, account_user_00.
Time-based partitioned tables: Names for partitioned tables related to time must follow the format table_name_time, where the time is represented as a 4- to 6-digit abbreviation. For example, account_user_2201.
Intermediate result tables: Temporary tables for intermediate results must follow the format tmp_table_name (full or abbreviated)_column_name (full or abbreviated)_creation_time. For example, tmp_account_tbluser_20220224.
Backup tables: Backup tables must follow the format bak_table_name (full or abbreviated)_column_name (full or abbreviated)_creation_time. For example, bak_account_tbluser_20220224.

Column naming conventions

Avoid special characters: Column names must not contain special characters. We recommend that column names do not start or end with an underscore (_), or start with a digit.
No reserved words or keywords: Column names must not include reserved words or database keywords.
Avoid numeric-only names: It is recommended that column names do not consist only of digits concatenated with underscores.
Use singular forms: Column names are recommended to be in singular form rather than plural.
Consistent letter case: It is recommended to use a consistent letter case for column names.
Clear semantics: Column names must clearly indicate their functionality, such as name.
Follow a structured format: Column names must include a functionality description or a general standard abbreviation, formatted as "business_name_functionality". Examples include task_num, create_date, station_desc, and task_id.

Structure design

Table structure design specifications

Normal table

Partitioned table

Prioritize business performance: When designing table structures, prioritize business performance rather than strictly adhering to the three normal forms. Introduce data redundancy where necessary to reduce table correlations and enhance performance. A redundant column must meet the following criteria:
- It is not frequently modified.
- It is not excessively long.
Specify a primary key: Every table must have a primary key. It is recommended to use a business column or a composite primary key instead of an auto-increment column as the primary key.

Tables in OceanBase Database are stored as index-organized tables (IOTs). If you do not specify a primary key, the system will automatically generate a hidden primary key for the table.

Note

Tables in MySQL Database are also IOTs, while tables in Oracle Database are not. For tables migrated from Oracle Database to OceanBase Database, it is recommended to use a unique index as the primary key to achieve a storage effect similar to Oracle tables. For example, in partitioned tables or Oracle heap tables, the primary key does not need to include the partitioning key.
Design columns: Each table must contain the gmt_create and gmt_modified columns of appropriate data types.

Note

Set the data type of gmt_create and gmt_modified to DATE (accurate to seconds) or TIMESTAMP WITH TIME ZONE (accurate to microseconds with current time zone information). You can use the SYSDATE or SYSTIMESTAMP function.
COMMENT attribute: Tables and columns must have the COMMENT attribute to help other developers better understand the data structure and meanings.
Column constraints: We recommend that you set a NOT NULL constraint for all columns in a table. You can customize the DEFAULT value based on business needs.
Column consistency: A column must have the same definition in different tables. Columns in a join must be of consistent data types to avoid implicit type conversion.
Complex types: We recommend that you do not use the binary large object (BLOB) or JSON data type.
Yes-or-no columns: We recommend that you use the unsigned TINYINT data type for yes-or-no columns. In this data type, 1 indicates yes and 0 indicates no.
- For example, for the is_deleted column that specifies whether to perform logical deletion, 1 specifies to perform logical deletion, and 0 specifies not to.
We recommend that you update the column comment in time after you modify the definition of a column or append a status value to the column.

Take note of the following considerations when you create a partitioned table:
- If the table contains a large amount of data and the accessed data is centralized, you can create a partitioned table.
- Observe the following notes for constraints on partitioned tables.
  - When you create a partitioned table, ensure that at least one column in each primary key and unique key is a partitioning key column of the table.
  - We recommend that you use a primary key wherever possible to ensure global uniqueness in a partitioned table.
  - A unique index on a partitioned table must include a partitioning key of the table.
We recommend that you design the partitioning strategy based on your practical use and scenarios.
- Practical use: history table and flow table
- Scenarios: tables with obvious access hotspots
To use partitioned tables, you need to use appropriate partitioning keys and partitioning strategies. Only recommendations on partitioning type selection are provided here. For more information, see About partitions.
- HASH partitioning: Select a column with a high degree of distinction and the highest frequency of occurrence in the query condition as the partitioning key for HASH partitioning.
- RANGE and LIST partitioning: Select an appropriate column as the partitioning key based on business rules. Ensure that the number of partitions is not too small. For example, for large log tables, you can use a time type column as a partitioning key for RANGE partitioning.
- RANGE partitioning: MAXVALUE cannot be the last column.
Limitations on partitions:

Range queries based on partitioning keys are not suitable in a HASH-partitioned table.
You can determine the number of partitions based on different logic:
- Based on the data volume: Generally, a large data volume requires more partitions to improve the query and write performance.
- Based on the parallel processing capacity of the system: You can determine the number of partitions based on hardware resources on the server, such as the number of CPU cores and memory size, so as to make full use of system resources.
- Based on the distributed scalability of the system: You can determine the number of partitions based on the system load and throughput to improve the system scalability and performance.
- Based on the data management granularity (namely, time): You can determine the number of partitions based on the time attributes of data. Specifically, you can divide partitions by time range to facilitate data management and query. For example, you can divide partitions by time granularity such as year, month, or day.

Column design specifications

You need to design columns based on data characteristics and requirements and select appropriate data types and constraints to ensure data accuracy and integrity.

Data types

MySQL mode

Oracle mode

Numeric columns

We recommend that you use the BIGINT data type, rather than integer data types such as INT and SMALLINT, to prevent the value range from being exceeded in the future.
Character columns
- We recommend that you use the VARCHAR(N) data type for all dynamic strings.
- Use CHAR(1) only for single-character columns. For yes-or-no columns, we recommend that you use the CHAR(1) data type to save space, with 1 indicating TRUE and 0 indicating FALSE. The column values must be consistent across all applications. For example, for the is_deleted column that specifies whether to perform logical deletion, 1 specifies to perform logical deletion, and 0 specifies not to.
  
  Notice
  
  NUMBER(1) can also express the yes-or-no concept but takes up more space.
- Do not use NVARCHAR or NCLOB as a column data type.
Notice

A column of the character data type can store all alphanumeric values. However, a column of the NUMBER data type can store only numeric values.
Datetime columns
- For columns requiring time accuracy, you can use the DATETIME(6) data type.
- For columns without requiring time accuracy, you can simply use the DATETIME data type.
- If a column may involve internationalization in the future, we recommend that you use the TIMESTAMP data type.
- We recommend that you do not use characters as the data types of time columns, which may cause implicit type conversion.
Recommendation on column selection

We recommend that you use the following methods, especially for large tables with millions of rows:
- The time columns of all tables in a business module must be unified. We recommend that you use the DATE data type. For business modules with higher requirements on precision, you can use the TIMESTAMP data type.
- We recommend that you use the NUMBER data type for numeric data storage to save storage space. Implement conversion at the frontend. Keep value ranges consistent with network segment data in size.
- You can store IPv4 and IPv6 data in different columns based on business needs by using the VARCHAR(N) data type.

Numeric columns

We recommend that you use the NUMBER data type for storage. When the NUMBER data type is used to store fixed-point numbers with a variable length and decimal precision, write the values in the format of NUMBER(p,s). When the NUMBER data type is used to store floating-point numbers, write the values in the format of NUMBER.
For decimal columns, we recommend that you use the DECIMAL data type and do not use the BINARY_FLOAT or BINARY_DOUBLE data type. If you use the BINARY_FLOAT and BINARY_DOUBLE data types, the issue of precision loss may occur in data storage, which may cause incorrect results in value comparison.
Priorities in implicit data type conversion

BINARY_DOUBLE has the highest priority and is followed by BINARY_FLOAT, which precedes NUMBER.

Value range of a numeric column

Type	Value range	Length (in bytes)
NUMBER	1.0E-130F to 1.0 E+126F (1.0E+126F excluded)	4 to 40
BINARY_FLOAT	1.17549E-38F to 3.40282E+38F	4
BINARY_DOUBLE	2.22507485850720E-308 to 1.79769313486231E+308	8

Character columns
- We recommend that you use VARCHAR2.
- OceanBase Database compares VARCHAR2 values without padding spaces and compares CHAR values with spaces padded.
- Datetime columns
  - The TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types store time zone information. Pay attention to the time zone difference.
  - We recommend that you do not use characters as the data types of time columns, which may cause implicit type conversion.

Others

You must use the BIGINT data type for auto-increment columns. It is prohibited to use the INT data type. This avoids storage overflow.
It is prohibited to update constraint definitions of table columns through foreign key self-referencing or cascade deletion. This avoids repeated deletion.
Avoid using the ENUM data type as far as possible, for example, ENUM('x','y','z'). You can use string types instead.
We recommend that you update the column comment in time after you modify the definition of a column or append a status value to the column.
When you design columns, maintain some redundancy to improve performance. However, you also need to take into consideration the synchronization of data. A redundancy column cannot be:
- A column subject to frequent modification
- An excessively long column
During implicit type conversion, numeric types have a lower priority than time types but a higher priority than characters and all other data types.

An appropriate character storage length saves database tablespace and index storage space, and more importantly, improves the retrieval speed.

Unsigned values avoid the storage of negative numbers by mistake and increase the expression range. We recommend that you use different data types for different value ranges. Here are some examples:

Object	Age range	Type	Expression range
Humankind	Within 150 years old	Unsigned TINYINT	Unsigned values: 0 to 255
Turtle	Hundreds of years old	Unsigned SMALLINT	Unsigned values: 0 to 65535
Dinosaur fossil	Tens of millions of years old	Unsigned INT	Unsigned values: 0 to 4.3 billion
Sun	5 billion years old	Unsigned BIGINT	Unsigned values: 0 to 1e+19

Best practices for database development