Skip to content

Make Everything Generic and Flexible?

October 27, 2011

Here are just two simple ways to make your database design more generic and flexible, which is not always good but in certain cases it makes sense and it pays to do in the long term.

First, is the preferences data, e.g. settings to your website, product, system, device etc. It should take care of any type of preferences/setting data and I have added some use cases based on iPhone device.

Preferences Data Model

Use case:

USER_ID: '90392847'
PRODUCT_ID: 'IPHONE'
CATEGORY_NAME: 'SETTINGS'
PREFERENCE_NAME: 'GENERAL'
DETAIL_NAME: 'NETWORK'
ATTRIBUTE_NAME: 'ENABLE 3G'         VALUE_TEXT: [Field name]
ATTRIBUTE_NAME: 'VALUE'             VALUE_TEXT: 'Y'
ATTRIBUTE_NAME: 'ATTRIBUTE STATE'   VALUE_TEXT: 'REQUIRED'
ATTRIBUTE_NAME: 'OPERATOR'         VALUE_TEXT: 'EQUALS'

USER_ID: '90392847'
PRODUCT_ID: 'IPHONE'
CATEGORY_NAME: 'SETTINGS'
PREFERENCE_NAME: 'GENERAL'
DETAIL_NAME: 'NETWORK'
ATTRIBUTE_NAME: 'CELLULAR DATA'      VALUE_TEXT: [Field name]
ATTRIBUTE_NAME: 'VALUE'             VALUE_TEXT: 'Y'
ATTRIBUTE_NAME: 'ATTRIBUTE STATE'    VALUE_TEXT: 'REQUIRED'
ATTRIBUTE_NAME: 'OPERATOR'         VALUE_TEXT: 'EQUALS'

Another useful design is whenever you have tons of reference type data such as list of country codes, state codes, status codes etc. it is good to keep them on the same table. Now, there are advantages ans disadvantages to that which I am describing below.

Reference Data Model

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 in 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

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: