Designing a database
Daniel Opitz
17 Jan 2017
Schema Naming Conventions
- Tables: english, lower_snake_case and plural.
- Columns: english, lower_snake_case and singular.
- Don’t use reserved words
- Every table must contain an auto increment primary key ‘id’ int(11)
- Foreign key names must be unique per database. Format: sourcetable_sourcefield_destinationtable_destinationfield
- The name of an index should match the field name. Multiple fields can be combined with _ (underscore).
- Views, Stored Procedures, Enums should be avoided. Use only tables.
- All tables should be normalized to the third normal form (3NF)
- Follow the SQL Style Guide by Simon Holywell
Best practice, performance tips
- Use LIMIT to preserve memory and speed up queries.
- Try to prevent sub-queries. MySQL sub-queries are very slow.
- JOINS with big tables are very slow. Maybe move more logic to PHP.
- Split big queries into small queries.
- Use an PHP array as buffer and optimize the query to a maximum.
- A query should not take longer then one second.
Default Engine/Encoding
- Engine: InnoDB
- CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
To make sure your strings go from PHP to MySQL as UTF-8, make sure your database and tables are all set to the utf8 character set and collation, and that you use the utf8 character set in the PDO connection string.
$pdo = new PDO('mysql:host=127.0.0.1;dbname=test;charset=utf8', 'root', '',
array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_PERSISTENT => false,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
)
);
Read more: https://stackoverflow.com/a/766996/1461181
Data types
- Primary and foreign keys: INT(11) signed (4 bytes, max id: 2’147’483’647)
- Strings: VARCHAR(255)
- Medium Text: TEXT (max. 64 KB)
- Text/Blob: LONGTEXT (max. 4 GB)
- Date-Time: DATETIME (Format: YYYY-MM-DD HH:II:SS)
- Boolean: TINYINT(1) NOT NULL DEFAULT ‘0’ (0 = false, 1 = true)
- Float: DOUBLE
- Exact monetary data: DECIMAL(15, 2)
Basic table
CREATE TABLE `tablename` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`created_at` DATETIME DEFAULT NULL,
`created_user_id` INT(11) DEFAULT NULL,
`updated_at` DATETIME DEFAULT NULL,
`updated_user_id` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `created_user_id` (`created_user_id`),
KEY `updated_user_id` (`updated_user_id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;
Basic codelist table (type)
CREATE TABLE `tablename_types` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`title` VARCHAR (255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` DATETIME DEFAULT NULL,
`created_user_id` INT(11) DEFAULT NULL,
`updated_at` DATETIME DEFAULT NULL,
`updated_user_id` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `created_user_id` (`created_user_id`),
KEY `updated_user_id` (`updated_user_id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;