Some best practices are described below:
- 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
- perform better than SQL statements since views are pre-compiled (but Oracle does cache statements)
- stored procedures perform better than views generally
- 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>
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"