To create stable systems that are responsive to change, essential ER models can be abstract—in ways that appear impractical to implement. The argument against such models is often “We couldn’t possibly create a database from that!” Your author disagrees. The generalization that makes the data models robust makes databases equally robust if they follow the structures.

Nevertheless, the design should not be completely “from that”. Design must account for the physical realities of a particular computing environment, and account for the way systems based on the design are expected to be used. The trick is to be controlled and cautious, so as not to lose the benefits of the generalized models. This presentation addresses steps required to convert an abstract data model into a database design. Beginning with the automated conversion, it then describes the steps required to fit the result onto a real computer with real performance constraints. Outline:

• Sub-types and super-types
• Abstraction, attributes, and parameters
• Derived attributes
• Physical constraints

The steps:

• Define usage patterns
• Resolve super-type / sub-type structure, based on usage patterns
• Convert the model using an automated tool.
• Optimize for performance, in terms of usage patterns.
• Deal with derived columns
• Convert stable, single-valued parameters to attributes
• De-normalize inherited and summary values
• Divide tables horizontally and/or vertically, based on expected usage patterns.
• Test performance under various assumptions.
• De-normalize as necessary
• Document rationale for everything.