Data Modeling on Steroids: Patterns and Reusability - PowerPoint PPT Presentation


PPT – Data Modeling on Steroids: Patterns and Reusability PowerPoint presentation | free to download - id: 56b18b-YzAxZ


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation

Data Modeling on Steroids: Patterns and Reusability


Data Modeling on Steroids: Patterns and Reusability Presented by Bert Scalzo, PhD About Quest Software What is Application Management? – PowerPoint PPT presentation

Number of Views:70
Avg rating:3.0/5.0
Slides: 32
Provided by: BertS6
Learn more at:


Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Data Modeling on Steroids: Patterns and Reusability

Data Modeling on Steroids Patterns and
Presented by Bert Scalzo, PhD
About the Author
  • Oracle DBA for 18 years, versions 4 through 10g
  • Worked for Oracle Education Consulting
  • Holds several Oracle Masters (DBA CASE)
  • BS, MS, PhD in Computer Science and also an MBA
  • LOMA insurance industry designations FLMI and
  • Books
  • The TOAD Handbook (March 2003)
  • Oracle DBA Guide to Data Warehousing and Star
    Schemas (June 2003)
  • TOAD Pocket Reference 2nd Edition (June 2005)
  • Articles
  • Oracle Magazine
  • Oracle Technology Network (OTN)
  • Oracle Informant
  • PC Week (now E-Magazine)
  • Linux Journal

About Quest Software
  • We provide application management solutions that
    enable customers to
  • Develop
  • Deploy
  • Manage
  • Maintain
  • enterprise applications without downtime

What is Application Management?
A holistic approach of managing the
entire application - not individual components
How Do We Do It?
We surround the application infrastructure
Application Monitoring
High Availability
Microsoft Infrastructure Management
Database Management
  • Definition of Pattern
  • Benefits of Designing for Reusability
  • Super and Sub Type Pattern
  • Intersection Patterns
  • Many to many relationships
  • Roles
  • Assemblies

World of Modeling
  • Improve process efficiency
  • Define/document Bus. Processes - create
    correct and complete application

Business Process Modeling (BPM)
  • End-user
  • IT Partner/Liaison
  • Business Analyst
  • Support for all UML diagrams - Analyze
    requirements - Design application
  • Reverse/forward engineer code

Object-Oriented Modeling (OOM - UML)
  • System Architect
  • System Analyst
  • App Developer
  • Identify all data relationships - E/R
    (Entity/Relship) diagrams - DB independent
  • Business Rules?

Conceptual Data Modeling (CDM E/R)
  • Bus. Analyst
  • Data Architect
  • Data Analyst
  • DB-specific model
  • Reverse engineer existing DB
  • Create/Update DB from model
  • Data Warehouse Modeling

Physical Data Modeling (PDM)
  • DBA
  • DB Developer
  • DB Architect

QDesigner synchronizes models from all levels in
a single tool
Basic Premises of Design Patterns
  • Not everything is specialized
  • Simply reuse whats reusable
  • Anyone can do this (really)
  • Not that hard to do technically
  • Merely requires some up front work

Not Everything is Custom
  • How many of your businesses are so specialized
    that you require custom modeling of everything?
  • We all have
  • Customers
  • Products
  • Contracts
  • Orders
  • Addresses
  • etc, etc, etc

These base concepts are often repeated within one
company across projects, departments, divisions
Side Effects of Customization
  • I had one insurance company for
  • Life and Accident
  • Automobile
  • Home Owners
  • Health Insurance
  • Administer 401K
  • Variable Annuity
  • I later worked for that company
  • Nearly 1000 entities
  • Close to 20,000 attributes
  • In a year, we simplified the model by 90!!!

Each statement was mailed using a different
address style (i.e. diff info) , and it took 6
separate change of address cards when I moved. So
I switched insurance companies
Benefits of Re-Usable Models
  • Takes time, but often yields
  • Better data models
  • More robust fundamental business knowledge
  • Better database design
  • More accurate implementation of business
  • Less rework
  • Avoids last minute implementation/coding
  • Less end-user re-training
  • Systems wont need to be reworked and redeployed
  • Centralized databases
  • All can be based upon common modeling best

Anyone Can Re-Use Models
  • Regardless of IS shop organization
  • Centralized Modeling group easy
  • Centralized Data Admin again, easy
  • Departmental or Project Oriented
  • Merely requires open communication ?
  • Helps to all use the same modeling tool
  • Helps for everyone to follow some fundamental
    modeling rules, guidelines and best practices
  • Can still work in a limited fashion with none of
    the above through grass roots efforts to work
  • Consultants simply require all data models be
    part of walk-throughs and deliverables (help them
    learn the business)

Simple Pattern 2 Constructs
  • Simply requires the appropriate, consistent and
    liberal use of just two very basic data modeling
  • Super and Sub Types
  • Bridging/Intersection Entities
  • NOTE There are two highly specialized
    intersection scenarios for making reusable
    modeling components
  • Roles
  • Assemblies (sometimes called B.O.M. solution)

Super and Sub Types
  • In the business world, we often find that things
    (i.e. entities) do not fall into simple, clear
    cut classifications
  • We sometimes find that things are very similar -
    with just a few differences
  • In data modeling, this can be referred to as
  • Entity Inheritance
  • Super and Sub Types
  • Generalization Hierarchies
  • The key question to ask is whether these
    different classifications are
  • Exclusive must be one way or the other
  • Inclusive can actually be all ways at once

Candidate for Super/Sub
Very similar entities in concept - some common
attributes, but also some differing attributes
Super/Sub Type Pattern
X means Exclusive
Super/Sub Meta-Data
This Super/Sub Type Meta-Data is only important
for transforming conceptual to physical model
Intersection Patterns
  • There are three ways to use intersections
  • Resolve basic many-to-many relationships
  • Not very intrinsically useful, but must mention
    for clarity
  • As Roles
  • Powerful concept
  • Can greatly reduce data model size
  • As Assemblies
  • Powerful concept
  • Can enable flexible database design

Resolve Many-To-Many
  • You CANNOT physically implement the concept of
    many-to- many relationships in a relational
  • You may potentially miss multiple key business
  • Many modeling tools will attempt to automatically
    resolve this

Intersection Meta-Data
Candidate for Roles
Unlike Super/Sub types, we now have identical or
near identical entities coming together into an
Poor Choice for Roles
  • Often, a first attempt to resolve the Role
    concept uses relationships. But there are two
    problems with this approach
  • What if Role participation requires additional
    attributes? Where would you place them?
  • What if business requirements change and you need
    to add or subtract Role participants? How do
    you later add and subtract foreign keys in

Solution for Roles
By creating a Role Type as entity rather than
attribute, adding/dropping Roles doesnt require
database change!
Candidate for Assemblies
Solution for Assemblies
This does everything the prior model does, with
the advantage of not being fixed in terms of what
things can be combined or in levels of
hierarchical combinations possible
Parts Minor Components Major Components Products
Assemblies Good for Anything
  • At insurance company, we used Assemblies to build
    dynamic product offerings comprised of
  • Features
  • Federal Rules
  • State Rules
  • etc, etc, etc
  • Real Benefit new products required no new
    application systems, just a few new rows of data
    in existing tables
  • Product time to market reduced by 50!!!

Assemblies Work
Component Type
How many base parts in a Gizmo? (nine)
Did You Notice ???
Roles and Assemblies are nearly the same thing
Parting Thoughts
  • Many Design problems have already been solved
  • Data Model constructs can be re-used as easily as
  • Carefully consider which business requirements
    are really unique
  • Must look for ways to reduce model complexity
    while making it generic
  • There are many other modeling issues this was
    just a start
  • Breaking models into sub-models
  • Round-trip Engineering
  • Conceptual -gt Physical Model compare and sync
  • Physical Model -gt Database compare and sync
  • Repository-based collaborative modeling
  • Horizontal and Vertical Partitioning
  • Data Warehousing (Star Schema design)
  • Object-Relational Mapping
  • etc, etc, etc

More Information
  • Free 45-Day Trial of QDesigner
  • http//
  • Compete enterprise modeling solution
  • Business Process Modeling (BPM)
  • Object-Oriented (UML) Modeling (OOM)
  • Conceptual Data Modeling (CDM)
  • Physical Data Modeling (PDM)
  • Save Upgrade your TOAD/SQL Nav license to
    the Suite
  • Suites include PDM!!!
  • Modeling White Papers
  • http//
  • Data Modeling Common Mistakes and Their Impact
  • Data Modeling It's Really All About the
  • Data Modeling Reality Requires Super and Sub