Database Best Practices

This is summarised from a book titled: Data Modeling
Some best practices are described below:


Database indexing

• index foreign keys
• index on columns with a lot of null values is useless
• frequently updated columns should not be indexed
• may not be a bad idea to use table scans for small tables (less than 1K rows)
• short-rowed tables (few columns) should use index-organised table
• b-tree index benefits performance if values are selective (distinct). The higher the index selectivity ratio, the better

Database views

• perform better than SQL statements since views are pre-compiled (but Oracle does cache statements)
• stored procedures perform better than views generally

Naming convention

• Constraint: <TableName>_<Type>_<ColumnName> where may be PK, FK, UQ (unique constraint), CK (check constraint)
• Index: <TableName>_<Type>_<ColumnName> where may be UX (unique index), IX (non-unique)
• View: <EntityOrTableName>_VW

Code table structure

Many applications require the use of code tables. Instead of creating many different code tables, an alternative is to create a generic code table structure.

The structure is as follows:

• CodeItem is used to store the code types
• CodeItemValue is used to store the code key-value pairs

An example of its use follows:

• codeItem.itemName = "Country"
• codeItemValue.codeValue = "SG"; codeItemValue.codeValueDesc = "Singapore"
• codeItemValue.codeValue = "MY"; codeItemValue.codeValueDesc = "Malaysia"