Blog Objective

This is a blog that attempts to make life easier by noting down the author's accrued knowledge and experiences.
The author has dealt with several IT projects (in Java EE and .NET) and is a specialist in system development.

03 September 2010

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"

No comments: