150
Part II: Building Databases and Working with Data
■ (^) The additional tables create additional work at every layer of the software.
■ Database relationships become more complex because what could have been a single
relationship are now multiple relationships. For example, instead of relating an
assembly process between any part, the assembly relationship must now relate with
multiple types of parts.
■ The database has now hard-coded the specifi c types of similar entities, making it
diffi cult to add another similar type of entity. Using the manufacturing example
again, if there’s an entity for every type of subassembly, adding another type of
subassembly means changes at every level of the software.
■ Coining a query to extract the proper set of data to meet reporting requirements is
now diffi cult and sometimes a daunting task due to the sheer number of tables that
are needed to fulfi ll the requirement.
The sweet spot in the middle generalizes, or combines, similar entities into single entities. This
approach creates a more fl exible and elegant database design that is easier to query and extend:
■ (^) Look for entities with similar attributes, or entities that share some attributes.
■ Look for types of entities that might have an additional similar entity added in the
future.
■ Look for entities that might be summarized together in reports.
When designing a generalized entity, two techniques are essential:
■ Use a lookup entity to organize the types of entities. For the manufacturing exam-
ple, a subassemblytype attribute would serve the purpose of organizing
the parts by subassembly type. Typically, this would be a foreign key to a
subassemblytype entity.
■ Typically, the different entity types that could be generalized together do have
some differences — which is why a purist view would want to segment them.
Employing the supertype/subtype (discussed in the “Data Design Patterns” section)
solves this dilemma perfectly.
Although generalization sounds like denormalization — it’s not. When generalizing, it’s
critical that the entities comply with all the rules of normalization.
Generalized databases tend to be data-driven, have fewer tables, and are easier to extend.
For example, an advertising company allowed the application architect to develop the
database. As a result, writing a query that returned customer information (fi rst name,
last name, address, phone, city, state, and so on) required accessing more than 40 tables
in one query. o mitigate the problem, the developer wrote a process that transformed and
loaded the data into a database that contained one-third of the number of tables as the
original. The same customer query could be written against the new database only requir-
ing the need of 10 tables. For which database would you rather write a stored procedure?
On the other hand, be careful when merging entities because they actually do share a root
meaning in the data. Don’t merge unlike entities just to save programming. The result will
be more complex programming.
c07.indd 150c07.indd 150 7/30/2012 4:18:11 PM7/30/2012 4:18:11 PM
http://www.it-ebooks.info