Aaron N Cutshall MSCIS MSHI Introduction to Relational

Aaron N. Cutshall, MSCIS, MSHI Introduction to Relational Database Design Patterns

Just who is this guy? Sr. Data Architect Local Group Leader Speaker – various events 30+ B. S. Computer Science M. S. Computer Information Systems M. S. Health Informatics Years

Something to consider… “A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools. ” – Douglas Adams (author of The Hitchhiker's Guide to the Galaxy)

Agenda • • • What are Design Patterns? Design Pattern Documentation Database Design Patterns Example Scenarios Closing Remarks

What are Design Patterns? • Time-tested solution templates • • Not a complete, finished design Not a plug-in class or library • Term refers to both • • The description of a solution that you can read An instance of that solution used to solve a problem • Structured approach to development • Between a programming paradigm and a concrete algorithm

What are Design Patterns? • Design Patterns: Elements of Reusable Object. Oriented Software (Eric Gamma, Richard Helm, Ralph Johnson, and John Vlissides) • • Referred to as the "Gang of Four" Targeted toward Object-Oriented Programming Initially had 23 design patterns Many more have been identified since • Head First Design Patterns: A Brain-Friendly Guide (Freeman, Bates, Sierra, Robson)

What are Design Patterns? • How are they useful? • Provide a way to solve issues using a proven solution • • • Facilitates the development of highly cohesive modules with minimal coupling Isolates the variability that may exist in the system requirements Makes the overall system easier to understand maintain • Make communication between designers and developers more efficient • • Breaks design into manageable chunks Purpose, form and function readily recognizable by pattern

What are Design Patterns? • Micro-Patterns • Breakdown design patterns into recognizable chunks • Stand at a lower, closer level to implementation • Can be thought of as the “features” of the design • Scalability • Solid design patterns can be used like Legos • Can be modified to suit specific needs • Systems build from an network of design patterns

What are Design Patterns? • Design Pattern Examples: • Chair • • • Purpose and basic form readily recognizable Specifics not needed to understand main purpose Micro-patterns identify features to overall pattern • Internal Combustion Engine • • • High variability in application (gasoline, diesel, size, purpose) Can be treated as a unit since internal details: • Do not obscure application intent • Do not need to be understood to be able to implement Most people understand function even if not how it works

Design Pattern Documentation • Design Patterns • Improves understanding to improve reuse • Ensures pattern is complete and useful Term Description Pattern Name Describes the essence of the pattern in a short, but expressive, name Intent Describes what the pattern does Also Known As List any synonyms for the pattern Motivation Provides an example of a problem and how the pattern solves that problem Applicability Lists the situations where the pattern is applicable Structure Set of diagrams of the classes and objects that depict the pattern Participants Describes the classes and objects that participate in the design pattern and their responsibilities Collaborations Describes how the participants collaborate to carry out their responsibilities Consequences Describes the forces that exist with the pattern and the benefits, trade-offs, and the variable that is isolated by the pattern credit

Design Pattern Documentation • Data Dictionary • Aids system documentation • Important to maintain consistency in database design • Improves productivity and reduces errors • Ensure consistent data type usage throughout (domain) Column Domain. Id (PK) Short. Name Description Data. Type. Id Scale Version. Id Column. Id (PK) Table. Id Seq. Nbr Short. Name Description Domain. Id Is. Primary. Key Foreign. Key. Id Is. Required Version. Id Table. Id (PK) Short. Name Description Schema. Id Version. Id Data. Type Version Schema Data. Type. Id (PK) Short. Name Description Use. Scale Use. Precision Version. Id (PK) Short. Name Description Effective. Date Expiration. Date Schema. Id (PK) Short. Name Description Version. Id

Design Pattern Documentation • Glossary of terms • Another aid to system documentation • Helpful for new people on projects • Aid for both internal and external documentation • Naming conventions • Consistency in database objects (data dictionary) • Consistency in application development • Improves productivity and reduces errors • Avoid religious wars regarding conventions

Design Pattern Documentation • Design Documents • • Details what is expected of the system Includes system and database architectures Treated as the blue-prints of the system to be built Also used for QA testing and validation • • System Design Document Template Database Design Document Template Detailed Technical Design Template • Resources

Design Pattern Documentation • Internal Documentation • • Document internal workings of system Ensures that results match design documents Identifies gaps in documentation or system Supports maintenance of other documentation Learning aid for new teammates Guides maintenance and extension efforts Provides comfort to management in case of team changes

Design Pattern Documentation • External Documentation • • Document system usage for end-user Supports purchase/implementation decisions Informs client/customer management Clean, consistent documentation • • Provides a polished, professional image Improves overall acceptability • Gives development team incentive for internal documentation

Mid-Point Review • Covered: • Definition of design patterns • Design pattern documentation • Coming up: • Design Patterns • • • Very complex subject for a one hour presentation Dozens of design patterns available Provide resources for you to find them • Example scenarios

Database Design Patterns • Atomic • Consistent • Isolate • Durable • Treat transactions as a single unit • Commit/rollback must be complete (“All or nothing”) • Data written must be valid according to all rules • Includes constraints, cascades, and foreign keys • No transaction should be influenced by another • Should be same as if transactions were serial • Once transaction is committed it will remain so • Transactions can be recovered if necessary

Database Design Patterns • On-Line Transaction Processing (OLTP) • Large number of small, frequent, high speed transactions (few records) • Typical operations: SELECT, INSERT, UPDATE, DELETE • Primarily for very fast query processing • Maintains data integrity in multiple-access environments (concurrency) • Effectiveness measured in transactions per second • Detailed and current data • Database schema is typically in Third Normal Form

Database Design Patterns • On-Line Analytical Processing (OLAP) • • • Low number of large, complex transactions (SELECT) Queries involve aggregations of large volumes of data Typically used for data mining and analytics Effectiveness measured in response time Historical data updated and aggregated periodically Database is typically denormalized in multidimensional schemas (such as star schema)

Database Design Patterns • Why OLTP and OLAP generally don’t mix • Different performance requirements • • OLTP many small simple transactions OLAP few large complex transactions • Different data modeling requirements • • OLTP is normalized with complex data model OLAP is denormalized with simple data model • Analysis requires data from multiple sources • • OLTP is focused on current data for particular processes OLAP integrates historical data from different processes

Database Design Patterns • How Hybrid OLTP and OLAP approaches can work • Capture OLTP transactions • • Not sequence oriented but separated by data classifications Often breaks data normalization rules • Perform pre-analytical calculations or breakdowns • • Identify population cohorts based on time and codification standards Excellent application for ratio analysis (numerator / denominator) • Load into a cube for reporting analysis • • Trend over time and under varying scenarios Results change as conditions and time frames change

Database Design Patterns • Resources: • OLTP: Online Transaction Processing Systems (Claybrook) • The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (Kimball) • Joe Celko's Analytics and OLAP in SQL

Database Design Patterns • Database Schemas introduced in SQL Server 2005 • Distinct namespace for database objects • Provides organization and separation • Improves management • Secures ownership capabilities • Resources: • SQL Server Best Practices: User-Defined Schemas • SQL Server Best Practices – Implementation of Database Object Schemas • Importance of Database Schemas in SQL Server

Database Design Patterns • Normalization • Organization of tables to eliminate data redundancy • Mainly serves the following purposes: • • Eliminate redundant data Ensure data dependencies make sense • 1 st Normal Form • Eliminate repeating groups in individual tables • Create a separate table for each set of related data • Identify each set of related data with a primary key

Database Design Patterns • 2 nd Normal Form • Create separate tables for sets of values that apply to multiple records • Relate these tables with a foreign key • 3 rd Normal Form • Eliminate fields that do not depend on the key • Note: Not always practical due to performance constraints

Database Design Patterns • 4 th Normal Form (Boyce Codd) • Rarely used in practical designs • Most situations do not call for that level of normalization • Resources • Description of the database normalization basics • A Simple Guide to Five Normal Forms in Relational Database Theory • Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

Database Design Patterns • Anti-Patterns: • Common patterns that cause problems (some hidden) • Highlight worst practices and present alternatives • Identify what to avoid (some are subjective) • Resources: • Ten Common Database Design Mistakes • SQL Antipatterns: Avoiding the Pitfalls of Database Programming • Anti. Patterns: Refactoring Software, Architectures, and Projects in Crisis

Database Design Patterns • Design Examples: • Free Industry-specific Data Models • Maria. DB Database Design Tutorial & Examples • Database Design 101: How to Create a Database Model for an Online Store • Database design with UML and SQL, 3 rd edition

Example Scenarios • High-velocity, low-volume transactions: OLTP • • • Capture and validate records ACID-compliant critical Referential constraints important High-concurrency (simultaneous transactions) Examples: • • • Web-site shopping carts Manufacturing facility bar-code captures Healthcare clinical transactions

Example Scenarios • High-velocity, low-volume transactions: OLTP

Example Scenarios • Low-velocity, high-volume transactions: OLAP • • • Static historical data in multiple perspectives Usually denormalized for query efficiency Based upon facts with multiple dimensions Data typically spread across multiple nodes Examples: • • • Sales Data Warehouse Historical Snapshots of transactional data (denormalized) Comparative Healthcare Research

Example Scenarios • Low-velocity, high-volume transactions: OLAP

Example Scenarios • High-velocity, high-volume transactions: Hybrid • • • Neither current nor historical – somewhere in between Data usually normalized but not always Data organized according to classifications Often serves as a bridge between OLTP and OLAP Examples: • • • Sociological survey and correlation analysis Laboratory materials testing and root-cause analysis Clinical Quality and Healthcare Utilization Measures

Example Scenarios • High-velocity, high-volume transactions: Hybrid

Closing Thoughts • Resources: • The Data Model Resource Book • • Vol. 1: A Library of Universal Data Models for All Enterprises Vol. 2: A Library of Data Models for Specific Industries Vol. 3: Universal Patterns for Data Modeling • • • Part I: Introduction Part II: Simple Database Design Part III: The Design Process A Quick-Start Tutorial on Relational Database Design Your Own Database Concept to Implementation 11 important database designing rules Databases from scratch

Questions & Comments BONUS: A TON of free e. Books from Microsoft, Red. Gate and Sentry. One! PRESENTATION FEEDBACK: • Your thoughts needed • Improve presentations • Make this event even more valuable!!! Aaron N. Cutshall @ancutshall aaron. n. cutshall http: //www. linkedin. com/in/aaroncutshall Aaron. N. Cutshall@gmail. com