MySQL database naming conventions.

1. Overview

This document lists out the Database standards recommendations to be followed by developers for Database design in MYSQL Technologies.

            MySQL 5.5.27 is a new version of the 5.5 production release of the world's most popular open source database. MySQL 5.5.27 is recommended for use on production systems.

2. Database design

·      Database configuration to include :

DBMS                         : Mysql 5.5

Data base Engine    : InnoDB

Collation                    : utf8_general_ci

·      If a data item has more than 2 data values to be made available for selection by end users, Create a Parameter table to store the different data values for the said data item.

·      Data width for data items with numeric data type for storage of Amount data should be (12,2)

·      Data width for data items with Text data type should be 50 or 100 instead of big & small.

·      All Tables need to include a field with constraint defined as Primary key with Auto increment setup. This field will be referred as Record ID with data type int and data width of 8 digits. Whenever there is a relationship between two Tables, this field should be made used as Foreign key.

·      HTML file to be made used to store error/warning  messages along with unique message ID.

·         Data type for each primary key column should be “ int”.

·         Use same variable type for similar fields across tables.

·         Use VARCHAR instead of CHAR. Because more space and performance effective

e.g., 

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	'    '	4 bytes	''	1 byte

·         For fixed length column as code use CHAR instead of VARCHAR

·         Specify data type and its size as per Functional Requirements.

·         All table should include the below 6 columns :

lld_rec_status	to identify active ( 1 ) & inactive ( 0 ) records
lld_createdby	To store login id of the user who has created the record.
lld_createddt	To store database server date on which the record was created.
lld_modifiedby	To store login id of the user who has modified the record.
lld_modifieddt	To store database server date on which the record was modified.
lld_ipaddress	To store ipaddress of the desktop which was made used by user.

3.  Naming Standards:

·      Database name should be same as Project name (for e.g., crm).

·      Schema name should be same as Database name (for e.g., crm)

·      All tables should be prefixed with Project Name and related module ( for e.g., roles to store roles details in crm Project ).

·      All master data table should suffix with table name _m.( for e.g., parameter_m)

·      Should not make use of spaces or any special characters in table name (Alpha number + Underscore are the only things allowed).

·      Column names should be prefixed by respective table name .

·      Column name should relate to the data being stored in the respective field .

·      Should not make use of spaces or any special characters to be used in column names (Alpha number + Underscore are the only things allowed).

·      Primary key column name should be “record_id” and prefixed with table name.( for e.g., ofo_rec_id).

·      Primary Key Field name to be prefixed with table name. (for e.g., ofo_rec_id ) with Primary key constraint.

·      Give names to all field level constraints.(for e.g., ofo_pk_rec_id, reg_fk_ofo_rec_id)

4. Normalization and Referential Integrity:

·      Use single column primary key in a table as much as possible.

·      Use unique constraints where ever required.

·      Use Referential integrity at Child Tables when ever required.

·      Avoid ON DELETE CASCADE

·      On-Line Transaction Processing (OLTP) should be at least 3NF.

(3 Normal Form: Every non-prime attribute is non-transitively dependent on every candidate key in the table. The attributes that do not contribute to the description of the primary key are removed from the table. In other words, no transitive dependency is allowed)

·      Evaluate every one-to-many relationship as a potential many-to-many relationship

5. General

• We should include primary keys, foreign keys and simple indexes to improve performance.
• Only use Unicode data types in very special circumstances.
•  Identify the critical tables and ensure minimum number of required records are available at implementation.
• Event, trigger, procedure should be prefixed with “project name_event_”, “projectname_trigger_”, project name_“proc_” followed by respective name