These guidelines define naming conventions for database fields. The aim is to have a more unique structure and to gain more database performance



First start with a few general naming conventions

• Table names should be singular (user, not users)
• The tables primary key should be called "table name" + "id" (i.e. userid)
• Field names should rather be CamelCase?, than using ""
• Foreign keys should, in most cases, be called just like the key, they refer to. (Exception: if the table refers twice to the same id. i.e. author -> userid and agent -> userid)

(unfortunately there are quite a lot of tables, that don't stick to these rules yet, but this will hopefully change in the future)

• Boolean fields (Checkboxes) need to be "tinyint(1)"
• IP fields need to be "int(11) unsigned", allow NULL-Values and be called "ip"
• Password fields need to be "char(32)"
• Fields, that are beeing searched as "fulltext" by an MS2-call (and only these!) need to have the "FUL"-Key set.

• Foreign keys should have the exact same type, as the primary one they refer to.
• Foreign keys should always have "IND"-Key set! (probably the most important performance rule)
• Fields filtered by in any where clause should have "IND"-Key set
• Use timestamp, instead of date = NOW() if a date should update on each Insert/Update.
• Primary key fields, in most cases, are "mediumint(8) unsigned". If this is not sufficent, use "int(11) unsigned"
• Define a "PRI"-Key for every table (except tables existing just for the reason to link two others)

• Remember to set the "reference"-tag, if you define a foreign key.
• Remember also to set the "on_delete"-tag then.

• DENY = do not allow to delete the primary key, if a corresponding foreign key exists here
• DELETE = delete this value, if the primary key is deleted
• ASK_DELETE = same as above, but inform the user
• SET0 = set this value to 0, if the primary key is deleted
• ASK_SET0 = same as above, but inform the user

These are all rules I can think of by now. Feel free to extend and discuss