Thursday, May 1, 2014
09:30 AM - 10:15 AM
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
- 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.
A veteran of the Information Industry since the days of punched cards, paper tape, and teletype machines (after getting his undergraduate degree in philosophy). Since getting his MBA in quantitative analysis, Dave Hay has been producing data models to support strategic information planning and requirements planning for over thirty years. He has worked in a variety of industries, including, among others, power generation, clinical pharmaceutical research, oil refining, banking, and broadcast.
He is President of Essential Strategies International, a consulting firm dedicated to helping clients define corporate information architecture, identify requirements, and plan strategies for the implementation of new systems.