Database Reference
In-Depth Information
Do Not Mix Heavily and Lightly Accessed Columns
. Much like
separating inactive and active data at the table level, tables containing
columns with vastly different rates of access can be separated. This
avoids continual physical scanning of rarely used data column values,
especially when those values do not contain nulls. This is one poten-
tially sensible use of 4NF.
Cache Data in Applications and Middle Tiers
. Direct database
access of static data values can often be avoided.
1.2.3
Different Forms of the Relational Data Model
The relational data model
2
has evolved from primary keys containing all
column values in all subsidiary child tables to modern use of surrogate pri-
mary and foreign keys, servicing object-coded online Java applications.
What are surrogate keys? Surrogate keys are abstracted identifying values
for table rows where the actual key values are complete abstractions to the
semantics or contents of the row values. Phew! I bet you would like that
one in English? For example, let's briefly describe a simple table containing
customers. Your customers could be identified by long, variable-length cus-
tomer names or even unwieldy and difficult to remember customer codes.
A surrogate key is an extra column added to the Customer table. The surro-
gate key is a generated integer value created by an Oracle sequence object,
whenever a new Customer row is added. Customers are later retrieved using
pick lists and transparent access to the surrogate key integer values. In other
words, you do not need to type in the number but simply pick the name
from a list. No typing of long names or codes is required, which is much
easier and more efficient!
So surrogate keys are generated for each row and are usually identifying
integers or pointers to table rows, perhaps somewhat similar to object iden-
tifiers. However, they are not object identifiers. Addressed object identifiers
should never be used in a relational database to identify an object in an
application. Surrogate key integers can be generated extremely efficiently in
Oracle Database using sequence generators (Oracle sequence objects).
Note:
Never use centralized tables to store the latest values of individual
sequence counters. This is nice for the application, but your database
might meet with its demise (die) from hot blocking that table. Your job
might join it!
