DotKernel Database Naming Conventions for MySQL

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

This entry was posted in DotKernel and tagged coding standard, DB. Bookmark the permalink.