Database Standards

Database Development

The Dark Jedi Brotherhood's preferred database is MySQL despite its extensive MS SQL Server database structure. All new database developments should be done in MySQL as a full migration is in process. As such, the Table and Field standards apply to MySQL.

Table Standards

Naming Conventions

All table names must be in lower case; must have words within the table name separated with underscores; and should include a prefix to segregate specific functions within the DJB. For example:

• General DJB tables are prefixed with db_
• Possession System tables are prefixed with dbpos_
• Competition specific tables are prefixed with dbcomp_

The remainder of the table's name should indicate its contents in singular rather than plural. Association tables should be in ownership order. For example:

• The user table for the DJB should be as follows: db_user
• The possession system's item table for the DJB should be as follows: dbpos_item
• The association table between users and items would be: dbpos_user_item
• The association table between users and units would be: db_user_unit

Note: For a hint on ownership order, assume the user is the owner in all user association tables.

Indexes

All tables should be indexed appropriately. Indexes speed up data retrieval (sometimes by an alarming amount) at the expense of some performance when inserting, updating, or deleting records. Primary Keys, Unique Indexes, Indexes, and Full Text Indexes all have their place. See the MySQL Index Documentation for more detailed information on index use.

Field Standards

Naming Conventions

Fields must be in all lowercase and have words separated by underscores. Like table names, field names should intuitively explain what data they hold. Some tips to live by:

• Base tables should not contain fields with its name prepended to them. For example: The db_user table should have fields named as follows: id, name, password, not user_id, user_name, user_password.
• Avoid keywords/reserved words as field names (like desc, asc, auto_increment, etc).
• Fields that reference other tables (typically in association tables) should include that table's name sans the prefix. For example: The dbpos_user_item table should contain the fields user_id, item_id.

Type Conventions

MySQL offers a plethora of field types. Each type incurs affects the size of the table. Use what is appropriate, do not overdo it.

Integers come with the most 'options'. Here are some tips:

• Available integers in MySQL are: TINYINT (1 byte), SMALLINT (2 bytes), MEDIUMINT (3 bytes), INT (4 bytes), BIGINT (8 bytes). Each allow for different length numbers (obviously).
• Make integer field unsigned where possible...it doubles the number of positive values!
• Specifying an integer length in a field definition is useless. A mediumint always takes up 3 bytes regardless of how you specify the length.

Database Manipulation Language (DML) Standards

RULE

Use all CAPITAL LETTERS for Common SQL commands. Ex: “SELECT Member_ID FROM Members WHERE…”

RATIONAL: Allows for increased SQL statement readability

RULE

Avoid “SELECT *” statements. Use “SELECT field1, field2, field3…”

RATIONAL: Allows for increased SQL statement readability and cuts down on transfer of unneeded data

RULE

Normalize tables where possible

RATIONAL: Increases the speed of data processing

RULE

Index columns that will be updated regularly and have a variety of options. Ex: Index a Rank Column, but not a Gender Column.

RATIONAL: Increases the speed of data processing and readability of table data

RULE

Minimize the use of not equal operations, <> or !=. SQL Server has to scan a table or index to find all values to see if they are not equal to the value given in the expression. Try rephrasing the expression using ranges: WHERE KeyColumn < 'TestValue' AND KeyColumn > 'TestValue'

RATIONAL: Increases the speed of data processing