Advanced Data Modelling: Communication, Consistency, and Complexity

Course Outline

• Conventions for the essential components: entities, relationships, attributes, and identifiers
• Effective naming and definition
• E-R Diagramming – symbol sets and their problems,  rules for readability and comprehension
• Types of data models – contextual, conceptual, logical, and physical
• Three types of data models before the physical database – contextual, conceptual, and logical
• The four Ds of data modelling – definition, dependency, detail, and demonstration

Working with higher-level models

• Contextual, conceptual, logical models – what they are, who they’re for, when we need them
• Definitions for each type of model, and common sources of confusion
• How the different kinds of data models relate to process, use case, and service models
• Avoiding the “deep dive into detail” – a three-phase method for data modelling
• How to start a large project with a contextual data model
• Guidelines for staying at the conceptual level, and how to tell when you’ve gone too far

Modelling time, history, and time-dependent business rules

• Historical vs. audit data, and when to show them on a data model
• “Do you need history?” – how to tell when your client is misleading you
• Four variations on capturing history in a data model
• Modelling time – special considerations for recording past, present, and future values
• Six questions you should always ask when a date range appears
• Thanks, Sarbanes-Oxley! Why we need “as-of reporting” and how to model data corrections

Correctly handling attributes

• The basic patterns – handling multi-valued, redundant, and constrained attributes
• Granularity – dealing with non-atomic and semantically overloaded attributes
• Dealing with reference data and the “classification vs. specification vs. instances” problem
• Three attributes that always need a qualifier
• Vector modelling – entity or attribute?

Modelling rules on relationships and associations 

• Using multi-way associations to handle complex rules
• “Use your words” – how assertions, scenarios, and other techniques will improve your modelling
• Associative entities – circular relationships, shared parentage, and other issues
• Alternatives for modelling constraints across relationships
• Advanced normal forms – how to quickly recognize potential 4NF and 5NF issues
• A simpler view – why the five normal forms could be reduced to three

Interesting structures – generalisation, recursion, and the two together

• Generalisation (subtyping) – when to use it, and when not to
• Generalisation with and without specification
• Guidelines for using recursive relationships
• Generalisation and recursion working hand-in-hand as a cure for literalism
• Recognizing lists, trees, and networks, and modelling them with recursive relationships
• Modelling difficult rules by combining generalisation (subtyping) and recursion
• Staying clear on generalisation vs. roles, states, and aggregation

Bridging the “E-R vs. Dimensional” divide – the world’s shortest course on dimensional modelling

• The perils of dimensional modelling without understanding the underlying E-R model
• Spotting facts and dimensions – the relationship between dimensional models and E-R models
• Saving time – building a first-cut dimensional model from an ER model

Better models through using data modelling in conjunction with other techniques

• Things, events, services, use cases, and processes – how they fit together and synergize
• The Weasel’s Guide to doing data modelling without anyone knowing it
• Event analysis as a rapid way to gather requirements
• Use Cases and Service Specifications, and their role in data modelling
• Process Modelling, and the vital role data models play

Interesting approaches and uses

• Developing a first-cut data model from business artifacts (forms, reports, screens, etc.)
• Living with legacy – the role of reverse-engineering and data profiling
• “Shock and dismay” – showing the business their current data model, and what it’s doing to them
• Where and how data modelling fits into selecting and implementing packaged applications
• The role of generic data models

Effectiveness skills for data modellers – communication, facilitation, presentation and consistency

• Preparing and delivering a data model review presentation
• Facilitation techniques specifically for the data modeller
• “The Magical Number Seven” and what it has to do with modelling
• Repeatable methods for discovering, assessing, and meeting new requirements
• A consistent approach – “scripts” to use while building a data model
• “Challenges” to use when validating a data model
• “Future-proofing” – what you can do to improve the lifespan of your model
• Seven techniques for “humanizing” data modelling and making data models more accessible