Code & Reference Tables
--------------------------------------------
Just because it is an entity on the logical data model...
- Does not always have to be a separate physical table
- Nor does it always require a physical RI relationship
"Not coded" information - store in base table anyway, forget code tables (Physical Database Implementation)
- Sometimes used for short lived data as a as permanent
- Where code description misspellings cannot occur
Code & Reference Tables - One option
--------------------------------------------
Combine code and reference tables into common groups
- Segmented tablespaces by characteristics
- Code "tables of tables" (See the diagram)
+ Do not cluster on primary index (spread data around)
+ Put index in separate buffer pool
Bottom Line
- Reduce number of objects ion memory
- Use segmented tablespace based on like characteristics
- Use tables of tables where applicable
- Less tablespace and indexes to manage
- Less backup and recovery issues
- Less Maintenance issues
Code & Reference Tables - Placement/Access
--------------------------------------------
Load small code tables into memory tables (application)
- Faster than buffer pool for null values needed to be displayed
- Buffer pool access at 0.1ms to 0.4ms
- I/O to fill the buffer pools at 10ms+for I/O index and data