SQL + Database Design Pattern Framework

SQL + Database Design Pattern Framework

Guide for developers and data architects building databases with SQL & Data Model patterns.

Building world-class databases with:

Standard Edition:
  SQL Design Patterns
  Data Model Patterns
  Data Access Patterns
  Data Persistence Patterns
  Data Experience Patterns
  Object-Relational Patterns
  Auditing & Archiving Patterns
  Normalization Patterns
  Anti-Patterns & Database Smells
  Real-World Reference Application
  Real-World Reference Database

PRO Edition:
  Supertype-Subtype Patterns
  Tree &; Hierarchy Patterns
  Meta Model Patterns
  Seed Data Model Patterns
  Mother of all Data Models

  Data Pattern Cheat Sheet
  SQL Coding and Naming Guide


The SQL Pattern Framework will help you:

  • Write more effective SQL
  • Design better data models
  • Build faster databases
  • Avoid common database mistakes.
  • Develop successful applications
  • Become a top 5% database expert
Understanding design patterns for sql and databases.
  • Evolution of SQL
  • What are Design Patterns?
  • What are Data Design Patterns?
  • Categories of Data Model Patterns
  • Building Applications with Databases  —  Sets vs Records, Full Stack, Real-world SaaS examples
  • Data Model Conventions and Style
  • SQL Conventions and Style
  • Reference Applications and Data Models
Laying a solid foundation for data models
  • Agile / Lean Data Models
  • ER Diagram Patterns
  • Making Databases User-Friendly
  • Primary Key Patterns
  • Foreign Key Patterns
  • Data Integrity Patterns  —  Entity, Domain, Referential, Business Entegrity, Cadcading Updates & Deletes
  • Relationship Types  —  1-1, 1-M, M-M, and Others
  • Choosing Data Types  —  Primary Key & Foreign Key Types, String Types, Numeric Types, Date/Time Types, Boolean Types, etc.
  • Anti Pattern: Stored Procedures
  • Anti Pattern: Triggers
  • Anti Pattern: Views
  • Anti Pattern: User-Defined Functions
  • Indexing Patterns  —  Cardinality, Clustered Index, Covered Index, Filtered Index
  • Does your Database Smell?  —  Value smells, Column smells, Table smells, Database smalls, Transaction Smells
Advanced data model patterns and best practices
  • Record Name Pattern
  • Short Name Pattern  —  Alias Pattern
  • Rollup Column Pattern
  • Sort Column Pattern
  • External ID Pattern
  • Missing Marker Pattern
  • Snapshot Pattern
  • Journaling Pattern
  • Enumeration Patterns  —  Check Constraints, Bit Columns, Lookup Tables
  • One True Lookup or MUCK Table Pattern
  • Name-Value Pattern
  • Polymorphic Association Pattern
  • Final Thoughts
Better models through (de)normalization
  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)
  • Boyce Codd Normal Form (BCNF)
  • Fourth Normal Form (4NF)
  • Fifth Normal Form (5NF)
  • Domain Key Normal Form (DKNF)
  • Sixth Normal Form (6NF)
  • De-normalization Pattern
  • Normalization Final Thoughts
Creating highly effective and efficient queries.
  • Transactions: Short & Sweet
  • OLTP vs OLAP
  • SQL SELECT Patterns
  • SQL INSERT Patterns
  • SQL UPDATE Patterns
  • SQL DELETE Patterns
  • WHERE Clause
  • Subquery Patterns
  • Date &Time Patterns
  • The IN Clause
  • Operator Performance  —  Positive Operators vs Negative Operators
  • NULL Value Pattern
  • Sargeable Queries
  • SQL Batch Patterns
  • Multiple ResultSet Pattern
  • Table Alias Pattern
  • SQL Namespaces
  • SQL Injection  —  Parameterized Queries, White Lists, Black Lists, User Input Constraints, Best Practices
  • Encryption Patterns
  • Timeout Patterns
  • Random Record Pattern
Building effective interfaces between applications and databases
  • Active Record Pattern
  • Repository Pattern
  • Data Mapper Pattern
  • Data Cache Patterns  —  Identity Map Pattern, Lazy Load Pattern, Eager Load Pattern
  • Unit-of-Work Pattern  —  Concurrency, Isolation Levels, Deadlock Patterns
  • Query Object Pattern
  • Null Object Pattern
  • Media Storage Pattern  —  How to store images, videos, documents, etc.
Friction of object-think versus relational-think
  • Impedence Mismatch  —  Cost, Complexity, Patterns
  • Object vs Relational
  • Persistence Ignorance
  • DDD – Domain Driven Design
  • OO Data Models  —  Single Table Inheritance, Concrete Table Inheritance, Class Table Inheritance
  • Object Successes  —  Workday.com
Creating delightful data interaction experiences for your users
  • UI/UX
  • Why UI/UX?
  • Thinking in CRUD  —  View, Add, Edit, Delete Cycle
  • DX Patterns  —  DX Design, DX Scaffolding, DX Customization, Related Lists
  • Search Patterns  —  Exact Search, Pattern Search, Text Search, Lucene.NET
  • Filter Patterns
  • Sort Patterns
  • Saved Views  —  Filter + Sort = Saved Views
  • Pagination Patterns
  • Modularity
  • Data Experience Review
Keeping track of data changes and historical data
  • Auditing Patterns
  • Logging Patterns  —  Activity Logging, Data Logging, Error Logging
  • History Patterns
  • Soft Delete Patterns
  • History Patterns  —  Reversing History, Undo/Redo
  • Archiving Pattern
Table inheritance and hierarchies
  • Single Supertype-Subtype Table
  • Multiple Supertype-Subtype Tables
  • Deeper Hierarchies
  • Table Inheritance Patterns
  • Single Table Inheritance Patterns
  • Class Table Inheritance Patterns
  • Concrete Table Inheritance Patterns
Data Models for Trees and Hierarchies
  • Adjacency Lists
  • Materialized Paths
  • Nested Sets
  • Trees in Action
The holy grail in data modeling
  • Meta Modeling Essentials
  • Meta Models vs Meta Data
  • Model Extensibility
  • Custom Column Pattern
  • EAV Patterns        —  EAV = Entity Attribute Value pattern
  • EAV with No constraints
  • EAV with Entity constraints
  • EAV with Attribute constraints
  • EAV with Value constraints
  • EAD Patterns        —  EAD = Entity Attribute Data pattern
  • EAD in Action
  • Hybrid SQL and NoSQL  —  Polyglot Persistence
  • Key Value Stores
  • Meta Model Success Stories  —  WordPress, Magento, Salesforce
  • Meta Modeling Review
Accelerating data modeling with Seed Models
  • Party Data Model  —  Modeling People and Organizations
  • Party Relationships
  • Party Locator
  • Party Model in Action
  • Party Models in the Real World  —  Siebel
  • Mother of all Models
  • Excellence Data Models
Valuable Reference Guide to Data Patterns
  • Essential Data Model Patterns
  • Advanced Data Model Patterns
  • Normalization Patterns
  • SQL & Data Access Patterns
  • Data Persistence Patterns
  • Object-Relational Patterns
  • Data Experience Patterns
  • Auditing & Archiving Patterns
  • Meta Model Patterns
  • Seed Data Model Patterns
Building consistency with good naming and coding conventions
  • General Naming Conventions
  • Database Naming
  • Schema Naming
  • Table Naming
  • Column Naming  —  Primary Key, Foreign Key, Data Types, Nullabiity, Audit
  • Index Naming
  • Naming Other Objects  —  Stored Procedures, Triggers, Views
  • SQL Coding and Formatting Conventions  —  Crafting Legible Queries
  • Using Table Aliases
  • Summary
Reference Applications

The SQL Pattern Framework comes with 2 comprehensive reference applications which allow you
to experience and explore the data patterns first hand in a real-world environment. 
These are screenshots of the ‘Training’ application which represents a fictitious software training company.

Actually see the benefits of Data Patterns

Many of the data patterns have a visual manifestation in the UI. Those that do are marked with a  pattern icon in the application.
Hovering over these icons will show which pattern is at work. As you can see below, many of these patterns have an impact on the user experience.

Below are screenshots of the ‘MetaPlus’ reference application.

This application will knock your socks off. The meta models in this application allow users to create their own entities and attributes. Essentially the user starts with a blank slate and then builds their own solution (referred to as citizen development).

This is made possible by implementing meta model patterns and doesn’t require any database or code changes! Very powerful.
(note: full documentation on meta models is only available in the PRO edition) 

Of course, data models and ER diagrams are available throughout the package.

Get started today

The SQL + Database Design Pattern Framework is a unique package that will show you how to build effective databases through patterns. You will learn to design, build, and access modern databases like a pro and gain a competitive edge, which you can build upon every day. But it takes action.

How to order

Ordering is easy.  Select the license you need and click ‘Order Now’.  Following payment you will receive a confirmation email with download instructions. Download, install, and you’re ready to go.

Your purchase includes a 1-year subscription which gives you FREE access to any new or updated releases during that period.

There is no risk. Your purchase is 100% backed with a full 60-day money back guarantee!  You will have plenty of time to explore and apply these patterns in your own work.

Nhận Xét Của Khách Hàng