Databases Reference
In-Depth Information
as well as their derivations, so that the link between them is not lost. There-
fore, denormalized entity identifiers increase the level of control and coor-
dination that is required to maintain shared business information.
Denormalized entity identifiers also make it difficult to define indexes for
the physical database designer. If the only basis for partitioning a data base
table is the beginning substring of the group data element, the index defi-
nition syntax becomes involved when the highest index key value for each
partition is defined. The remaining characters of each partition's highest
index key value must be padded with either blanks or high values. For
example, if a table was partitioned on area code, the index key value for the
partition that contains the residential customer accounts for Virginia
would have to be defined as either “703” or “703999999999.” A denormal-
ized entity identifier also does not allow the data base designer to define a
clustering index on the area code, without the-rest of the group data ele-
ment, in combination with any other column.
Programming a join between two tables is also difficult. If one table has
implemented atomic data elements as the entity identifier and another
table, sometimes from another application area, and has the foreign key rep-
resented by a group data element, problems may occur. When any one of
the atomic data elements has a physical domain that is different from that
of the group data element, such relational operations as joins are either
impossible or perform poorly. Even if two columns have comparable but
not identical data types, the comparison has to involve a data conversion.
Even if the physical domains are an exact match, the use of a group data
element by one application and the atomic data elements by another com-
plicates the join-structured query language (SQL). Joins must be estab-
lished by the use of the scalar function SUBSTR for the column that con-
tains the group data element or the concatenation operator for the
columns that contain the atomic data elements, as shown in the following
WHERE clauses:
WHERE SUBSTR (CUST_ACCT_NO, 1,3) = AREA_CD
WHERE CUST_ACCT_NO =
AREA_CD LCL_EXCH_CD LINE_NO CUST_CD
Similar to the comparison of columns, concatenation of columns can
only occur if both are compatible strings. It is not possible to concatenate
a character column with a decimal column, so the data administrator must
ensure that the atomic data element columns are all of the same type as the
original group data elements. If one atomic data element is null, concatena-
tion of a nun column with a non-null column results in a null value and
yields unpredictable results. If the group data element cannot be null, then
a join on the column that contains the group data element and the concat-
Search WWH ::
Custom Search