PATTERNS OF DATA MODELING- P46: Models provide the means for building quality software in a predictable manner. Models let developers think deeply about software and cope with large size and complexity. Developers can think abstractly before becoming enmeshed in the details of writing code. Although models are beneficial, they can be difficult to construct. That is where patterns come in. Patterns provide building blocks that help developers construct models faster and better. | Design Identity 213 UML model IDEF1X model Region AccountType regionlD accountTypeName regionName 1 Bank 1 Account bankID 1 accountID bankName regionlD fK bankID FK accountNum accountTypeName FK Figure Existence-based lookups recommended Example. Another option is to use a mnemonic abbreviation for lookups and existence-based identity for everything else. Value-based identity. A combination of application attributes identify each entity. In Figure names identify Region Bank and AccountType. Account is identified by bankName combined with accountNum. An advantage of value-based identity is that primary keys have intrinsic meaning. However primary key values can be difficult to change a change to a primary key may require propagation to many foreign keys. Some entities lack natural real-world identifiers. Also primary keys can become lengthy from the foreign keys of a series of related tables as Figure illustrates. Figure shows an awkward situation for Transaction. With value-based identity and referential integrity the two paths lead to two copies of bankID and accountNumber. With value-based identity developers must abandon referential integrity to have a single copy of the fields. Hybrid identity. A schema can combine existence-based identity with value-based identity. In Figure Region Bank and AccountType have artificial identity and Account has identity derived from a bank reference combined with an account number. Propagated identity. Identity can also propagate from one entity type to another. In Figure the primary key of Exercise is also the primary key for Answer. A book can have many exercises an exercise may have an answer. This is seldom a good idea as there is a leakage of information from one entity type to the other. 214 Chapter 16 Relational Database Design UML model Region AccountType regionName IDEF1X model --------1--- Bank bankName regionName FK UML model Figure Value-based .
