This document outlines the standards for creating new database tables within the Axion project. Adhering to these standards is crucial for ensuring data integrity, performance, and compatibility across the application.
InnoDBAll new tables must use the InnoDB storage engine.
InnoDB?InnoDB is the default and most robust storage engine for modern MySQL and MariaDB applications. It provides several critical features that are essential for our project:
InnoDB locks only the specific rows that are being modified, whereas older engines like MyISAM or Aria lock the entire table. Row-level locking allows for much higher concurrency and performance, especially in a system with many simultaneous users or processes.For these reasons, InnoDB is the required standard, replacing previous usage of Aria or MyISAM.
All new tables must use the utf8mb4 character set with the utf8mb4_unicode_ci collation.
utf8mb4utf8mb4 is a 4-byte character set that supports the full range of Unicode characters. This is essential for handling international languages, special symbols, and emojis without causing data corruption or errors. It is the standard for modern web applications.utf8mb4_unicode_ciCollation defines the rules for sorting and comparing strings. While utf8mb4_unicode_ci was a common choice in the past, utf8mb4_unicode_ci is now the required standard.
utf8mb4_unicode_ci is based on the official Unicode Collation Algorithm (UCA). This ensures linguistically accurate sorting and comparison for a vast range of languages. For example, it correctly handles complex character mappings (like the German ß being equivalent to ss).utf8mb4_general_ci? The general_ci collation uses a simplified, older set of sorting rules. While it can be marginally faster, it is less accurate and can produce incorrect sorting results for certain languages or characters. The minor performance difference is a negligible trade-off for the significant gain in correctness and reliability that unicode_ci provides.All CREATE TABLE statements should explicitly define the engine and character set/collation as follows:
CREATE TABLE `example_table` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`description` TEXT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;