Database Skills: Complete Contents

This posting is updated whenever a new post goes up.

There is a also a Skills-oriented Table Of Contents. It is not as complete as this list, which lists all posts, but it is more centered on links as they relate to skills.

If you want some free analysis, why not submit your schema to the Database Programmer? If you are willing to discuss your issues with a bit of public exposure, I will provide free analysis, and everybody can benefit!

User-Submitted Analysis Topic: Email

The Application Stack

• A Working Definition of Business Logic, With Implications for CRUD Code
• The Cost of Round Trips To The Server
• Critical Analysis of An Algorithm: Sproc, Embedded SQL, ORM
• Historical Perspective of ORM and Alternatives
• The Framework And The Database
• Data And Code At The Application Level
• Approaches to UPSERT
• Why I Do Not Use ORM

Table Design Basics: Keys, Normalization, Denormalization

The first group of posts introduces the must-know terms and techniques for table design.

It might be a good idea to start with The Relational Model.

• Introduction (spell it out or figure it out)
• Primary Keys
• Foreign Keys
• First Normal Form
• Second Normal Form
• Third Normal Form and Calculated Values
• Of Tables and Constraints (also listed below in Table Design Patterns)

Following up on the normal forms are some basic discussions of normalization and denormalization.

• The Argument For Normalization
• The Argument For Denormalization
• Revisiting Normalization And Denormalization
• Denormalization Patterns
• Keeping Denormalized Values Correct
• The Data Dictionary and Calculations, Part 1
• The Data Dictionary and Calculations, Part 2

Table Design Patterns

The second subseries details commonly occurring patterns in table design, how to recognize them and when to use them.

There is a complete List of Table Design Patterns. The rest of the entries are:

• A Sane Approach To Choosing Primary Keys
• Different Foreign Keys for Different Primary Keys
• Cross Reference Validation Pattern
• Limited Transaction Pattern
• False Patterns and the Reverse Foreign Key
• The Primary Key That Wasn't: Impermanent Primary Keys
• Resolutions (Also listed in Queries below)
• Of Tables And Constraints (Also listed above in keys, normalization, denormalization)
• How the SQL UNION Affects Table Design (Also listed below in Queries)
• History Tables
• Secure Password Resets
• A Case When Table Design is Easy and Predictable (combinatorial and maximum complexity)

SQL SELECT and Queries

• Introduction To Queries
• GROUP BY, HAVING, SUM, AVG and COUNT(*)
• How SQL UNION Affects Table Design
• The JOIN is the Cornerstone of Powerful Queries
• Joins Part Two, The Many Forms of JOIN
• Resolutions (Also listed in Table Designs Above)
• Recursive Queries With Common Table Expressions
• The Really Cool NTILE() Window Function

Algorithms and Processes

• Sequencing Dependencies

Server-Side Code

• Triggers, Encapsulation and Composition
• Loops Without Cursors

Analysis

• The Requirements Are Always Wrong
• This Application Has Unique Business Rule Needs

Development Cycle

• Table Structure Changes
• Dictionary Based Database Upgrades
• Upgrading Indexes With A Data Dictionary

Philosophy

• Minimize Code, Maximize Data
• The Quest For The Absolute
• I Am But A Humble Filing Clerk
• Triggers, Encapsulation and Composition (repeated here because there is some philosophy in there)

Data Dictionary

Many of these posts are listed elsewhere in this table of contents, but I wanted to have them altogether in one place as well.

• Using a Data Dictionary
• The Data Dictionary and Calculations, Part 1
• The Data Dictionary and Calculations, Part 2
• Dictionary Based Database Upgrades
• Upgrading Indexes With A Data Dictionary

Security

• Introducing Database Security
• A Comprehensive Database Security Model
• Secure Password Resets

Performance

• Huge Inserts
• Pay Me Now or Pay Me Later
• The Web Layer

The Browser

• The Wonderful Awful Browser
• Javascript As a Foreign Language