Back to Blog
Dotkernel

DotKernel Database Naming Conventions for MySQL

By admin  ·  Mar 10, 2010
DotKernel borrows the database naming conventions from FaZend: Rules of naming of database tables and columns. FaZend is an open-source PHP framework based on Zend Framework. Database naming conventions for tables and columns:
  • Singular table names only (e.g. user, category, product, order, orderProduct)
  • Every table must have an auto-incrementing integer column id
  • ZF-like names of columns and tables (e.g. user::isAdmin, orderProduct::product)
  • Foreign keys must have the same names as reference tables
  • Foreign keys must have the same names as reference tables + the name of the referenced column Example: table referenced: admin , column name: Id so the column will be adminId
  • Pattern for CONSTRAINT name : FK_referencedTableName_tableName Example: CONSTRAINT `FK_admin_adminLogin`
  • SQL keywords are capitalized (e.g. SELECT, INT)
Example of proper SQL file formatting and naming: 
>CREATE TABLE IF NOT EXISTS `user`
 (
   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   `username` VARCHAR(255) NOT NULL,
   `password` VARCHAR(25) NOT NULL,
   `email` VARCHAR(100) NOT NULL,
   `firstName` VARCHAR(255) NOT NULL,
   `lastName` VARCHAR(255) NOT NULL,
   `dateCreated` TIMESTAMP NOT  NULL DEFAULT  CURRENT_TIMESTAMP,
   `userType` INT(11) NOT NULL AUTO_INCREMENT
   `isActive` ENUM('0','1') NOT NULL DEFAULT '1',
   PRIMARY KEY  (`id`),
   UNIQUE KEY `username` (`username`),
   UNIQUE KEY `email` (`email`)
   CONSTRAINT `FK_user_userType` FOREIGN KEY(`userTypeId`) REFERENCES `userType`(`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
 )
 ENGINE=InnoDB
 DEFAULT CHARSET=latin1
 AUTO_INCREMENT=1 ;
Conclusion: The names of database tables and columns must follow camelLetter as naming conventions.s