Database Coding Standards

• Table names are all lowercase use “_” as word separator. Table names are plural.

Examples:

• users
• user_addresses
• Transactions
• product_colors

• Do not use SQL reserved word for table name

For creating many-to-many relations the names of the tables are concatenated, with X as separator:

Example:

• store_productXproduct_colors
• authXusers

• Column names have the same namimg rules as table names.
• For primary key should be named “id”
• All tables must have primary key, separate from all other keys. The exception is if the table is the map table for many-to-many relationship.

• Name the indexes after the column indexed, if it is a single column index.

• Use VARCHAR for ordinary text columns. For longer text use TEXT or MEDIUMTEXT.
• For password fields make the text columns binary, so that they match case-sensitive during lookups.
• If password is md5 or any other checksum make the column case insensitive
• For primary keys use UNSIGNED INTEGER(11) AUTO_INCREMENT.
• For statues, types, and other columns that can have a small number of positive integer values use UNSIGNED TINYINT
• For IPv4 address use UNSIGNED INTEGER(11), since the IP addresses are 32-bit integers.
• For timestamps in seconds use UNSIGNED INTEGER(11), not the TIMESTAMP type.
• For money fields usually DECIMAL(10,2) works great.