|
You must unlearn what you have learned. This article contains obsolete data. It reflects a Dark Brotherhood policy, position, or object that has been removed or replaced. It is preserved here for historical purposes only and should not be used/referenced.
|
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.
Normalization
Table structures should be normalized as much as is reasonable. The primary benefits to normalization are:
- The reduction of anomalies during database modification
- Minimize redesign effort when extending the database
- Makes the data model more informative
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
SQL is a vital piece of interacting with any of the Brotherhood's databases. To ensure readability, the following standards should be adhered to where possible:
Readability
Readability is key with SQL statements as they can be quite complex. To aid in the readability, using a standard format across all DJB scripts that interact with the database is a good plan. The Brotherhood has adopted the following standards:
- Use all CAPITAL LETTERS for SQL keywords. e.g. SELECT, FROM, WHERE, ORDER BY, etc.
- Use all lower letters for table and field names.
Performance
Below are a list of some performance related tips with SQL:
- Be specific! Avoid "SELECT *" statements where possible (and reasonable), using "SELECT field1, field2, field3..." instead. This increases statement readability at the same time as reducing the transfer of unneeded data.
- Equal > Not Equal! Minimize the use of not equal operations, <> or !=. Queries that include those operations have 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 key_column < 'TestValue' AND key_column > 'TestValue'