Database Naming Convention
Rules of naming of database tables and columns
There are few important rules (more technical details you can find in description of FaZend_Deployer and FaZend_Db_Table_ActiveRow):
- Singular table names only (e.g. user, project, city)
- Every table must have an auto-incrementing integer column id
- ZF-like names of columns and tables (e.g., user::isAdmin, userFile::pdfAttachment)
- Foreign keys must have the same names as referenced tables
- SQL keywords are capitalized (e.g. SELECT, INT)
- Many-to-many relations have combined names (e.g. userFile, projectMember)
Good example of proper SQL file formatting and naming:
--
-- One project under construction
-- @see Model_Project
--
CREATE TABLE IF NOT EXISTS `project`
(
`id` INT NOT NULL AUTO_INCREMENT
COMMENT "Unique ID of the project",
`name` VARCHAR(120) NOT NULL
COMMENT "Unique title of the project",
`user` INT NOT NULL
COMMENT "Project owner and operator",
PRIMARY KEY(`id`),
UNIQUE(`name`),
FOREIGN KEY(`user`) REFERENCES `user`(`id`)
ON UPDATE CASCADE
ON DELETE CASCADE
)
AUTO_INCREMENT=1
DEFAULT CHARSET=utf8
ENGINE=InnoDB
COMMENT="Software development project done by us";
Some examples of right and wrong names of tables, views, and columns:
| Wrong | Right | Why? |
|---|---|---|
| users | user | Only singular names of tables are allowed |
| PhotoImage | photoImage | First letter is always small |
| project_user | projectUser | No underscores are allowed |
| is_approved | isApproved | No underscores |
Copyright Notice: The article is published by FaZend.com and is protected by US and International copyright laws. You may not republish, copy, reproduce or distribute this article or its paragraphs or elements. You may reference the article in your documentation with a mandatory notice about the authorship of the material. If you have any other privacy concerns about the materials published on FaZend.com website you shall email to privacy@fazend.com.
