Project

General

Profile

Actions

Database Conventions

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

Updated by Sven Vanpoucke over 11 years ago ยท 2 revisions