Database Conventions¶

• Table of contents
• Database Conventions
  • Names
  • Datatypes
  • Indexes
  • Performance tips

As it is the case with the code we also need some conventions about the database. The following list provides a set of rules which improve the readability / performance of the database

Names¶

• Table names
  • Should always start with the prefix of the application (ex: user_)
  • Should always be in lowercase with underscores (ex: user_user)
• Field names
  • Should always be in lowercase with underscores (ex: start_time)
  • When the field is referencing to another table the postfix id should be added (ex: user_id)

Datatypes¶

• The field should always be in the datatype that uses the minimum amount of space on the system. (ex: use a boolean instead of a tinyint with 1 length)
• When using text fields we should decide what the specific length will be
  • No length: use a text field
  • Length bigger then 255 characters: use a varchar
  • Length smaller or equal to 255 characters: use a char

Indexes¶

• Put indexes on all the primary keys
• Put indexes on all the reference fields
• Put indexes to a combination of fields when this combination is used in a query that is called a lot or in a lot of queries.

Performance tips¶

• Sometimes a de-normalized table is faster then a normalized table.
• The order of your fields is important. Always start with your smallest data types and put the columns that are text based on the last columns