Databases Reference
In-Depth Information
Table 7-2.
(
continued
)
Recommendation
Reasoning
If you have a business rule that defines the length and
precision of a number field, then enforce it; for example,
NUMBER(7,2)
. If you don't have a business rule, make it
NUMBER(38)
.
Enforces a business rule and keeps the data cleaner
Follows Oracle's recommendation of using
VARCHAR2
for character data (instead of
VARCHAR
). The Oracle
documentation states that in the future,
VARCHAR
will be
redefined as a separate data type.
For character data that are of variable length, use
VARCHAR2
(and not
VARCHAR
).
For character data, specify the size in
CHAR
; for example,
VARCHAR(30 CHAR)
.
When working with multibyte data, you'll get more
predictable results, as multibyte characters are usually
stored in more than1B.
If you have a business rule that specifies the maximum
length of a column, then use that length, as opposed to
making all columns
VARCHAR2(4000)
.
Enforces a business rule and keeps the data cleaner
Use
DATE
and
TIMESTAMP
data types appropriately.
Enforces a business rule, ensures that the data are of the
appropriate format, and allows for the greatest flexibility
when using SQL date functions
Specify a separate tablespace for the table and indexes.
Let the table and indexes inherit storage attributes from
the tablespaces.
Simplifies administration and maintenance
Most tables should be created with a primary key.
Enforces a business rule and allows you to uniquely
identify each row
Create a numeric surrogate key to be the primary key for
each table. Populate the surrogate key from a sequence.
Makes joins easier and more efficient
Create primary key constraints out of line.
Allows you more flexibility when creating the primary
key, especially if you have a situation in which the
primary key consists of multiple columns
Create a unique key for the logical user—a recognizable
combination of columns that makes a row one of a kind.
Enforces a business rule and keeps the data cleaner
Create comments for the tables and columns.
Helps document the application and eases maintenance
Avoid LOB data types if possible.
Prevents maintenance issues associated with LOB
columns, such as unexpected growth and performance
issues when copying.
If a column should always have a value, then enforce it
with a
NOT NULL
constraint.
Enforces a business rule and keeps the data cleaner
Create audit-type columns, such as
CREATE_DTT
and
UPDATE_DTT
, that are automatically populated with default
values or triggers, or both.
Helps with maintenance and determining when data
were inserted or updated, or both. Other types of audit
columns to consider include the users that inserted and
updated the row.
Use check constraints where appropriate.
Enforces a business rule and keeps the data cleaner
Define foreign keys where appropriate.
Enforces a business rule and keeps the data cleaner
