SqlCredit - Developing a Complete SQL Server OLTP Database Project

January 29, 2007

Part 1: Introduction, Business Problem, and Initial Design

Series Introduction

The purpose of the SqlCredit series is to demonstrate the database design and development portion of a development project. We will build a complete, functioning, tested credit card database to illustrate the complete software development lifecycle. Microsoft provides us with AdventureWorks, but all we get to see is the final product. This series will be about the process that results in a well-designed, well-coded, and well-tested database.

Part of the plan is to do an initial design that actually has some built-in “refactoring opportunities” (bits that we will fix later in the series). But overall, the design should be solid, scalable, well-coded, and well-tested. When the series is complete, the code-base will allow a reader to create a complete database with tables, stored procedures, triggers, functions, automated unit tests, and sample data by running a simple script. This is not an academic exercise; it is a pragmatic design based on goals of scalability, performance, and quality.

We? Who’s “We”?

This project will use agile methodologies (1, 2) and scrum, so the series must be interactive. At different times you, the reader, will be asked to play the role of development team member (code reviews and sprint reviews), QA (testing and writing unit tests), and product owner (prioritizing backlog items). You may not like all the decisions that are made, but I doubt you have ever been on a project where you liked all the decisions … even if you were the only developer.

Database Journal has set up a forum specifically for this series here. It is open now for you to provide feedback, suggest changes, weigh in on project priorities, and eventually, to provide automated unit tests. One thing I am really looking forward to is having people say, “I have faced that same issue and solved it this way ….”

I don’t claim to know everything there is to know about OLTP design and coding. I have done quite a bit of it. As part of my work at Corillian, I have helped to develop databases that are in use at some of the largest banks and credit unions in the country. However, part of my motivation for writing this series is to learn from the readers so that I can hone my design and coding skills.

The Business Problem: SqlCredit

The problem we will tackle is a credit card database for a start-up credit card company called SqlCredit. This database will need to store information about accounts, people, cards, vendors, and transactions (purchases).

Partly to make things interesting, and partly because this is always a hard business problem, we will state that accounts have one to n persons and also that accounts have zero to n cards. Therefore, a husband and wife can each have their own card numbers but the same “account.” They may also have two physical cards that share the same number. The database design will not dictate how SqlCredit chooses to issue cards. (Cards will have three-digit security codes, so the natural key for a card will be the combination of card number and security code.)

Project Kick-Off

We have spent time working with our product owner and researching on our own, so we have a good idea of what we need to build. Let’s get started with some initial database schema design and overall design decisions.

Objects to Be Modeled:

• Account
• Person (cardholder)
• Credit Card
• Card Type (e.g. “Gold”, “Platinum”)
• Card Partner (e.g. “Alaska Airlines”, “Lexus”)
• Vendor (the merchant where the purchase was made)
• Purchase (individual credit card transaction)

Design Decisions/Requirements:

• An account must have at least one cardholder
• An account can have multiple cardholders
• Each account has a “primary” cardholder (the person whose name shows on the bill and who is the first contact point for any issues)
• Each cardholder is issued a physical card with a unique combination of card number and security code
• Address is an attribute of an account
• Phone number is an attribute of a person, not an account
• The vendor table is much simpler than would be required for a real application, but this piece was simplified for the sake of brevity
• Each card must have exactly one card type
• Cards may or may not have a card partner (0 or 1)
• No need to support UNICODE characters
• US dollar sales only
• US addresses only
• Delay implementation of history records
• History will not be required for transactions or vendors
• Built on SQL Server 2005 but not using 2005-specific features until later in the series

Coding Standards, Error Reporting, Unit Tests

Error Reporting

I wrote an extensive article covering stored procedure error checking and reporting. The stored procedures in this series will be based on that pattern and will include full error checking and reporting (not always “level 3” but at least “level 2”).

Triggers and History Records

I have always steered away from writing triggers in my databases. I don’t like the idea of code being fired that is not obvious from the code being called directly. However, at the PASS 2006 conference, I attended a class given by Joy Mundy. She is a business intelligence expert and spoke about feeding data from an OLTP system into an OLAP system.

As a BI designer, she puts more trust in a transactional system if the updates are tracked using triggers versus stored procedures. There are too many opportunities for inserts and updates executed outside of the standard stored procedures. Yes, triggers can be disabled also, but it is much less likely that someone would explicitly disable a trigger than it is that they would update a record using an ad-hoc query or some bit of old code that does not call the standard stored procedure. According to Joy, “Education of DBAs + a trigger works much better for me than a stored procedure.”

Because it is likely that whatever transactional system you build will eventually feed a business intelligence system, I recommend taking Joy’s advice and building these triggers for tracking history.

Automated Testing

TSqlTest is a SQL Server unit test framework that I developed as part of my work at Corillian. It is available at TSqlTest.org. Automated unit tests for the project will utilize TSqlTest. The project will not be “test-driven,” but automated tests will be written early in the project.

Development Standards

The code will be developed using the development standards here. Yes, I wrote those too.

Feel free to provide feedback on any of these pieces through the forum.

Next Month

• Real code!
• Database and table creation, initial CRUD procedures (“create”, “read”, “update”, and “delete”)
• Comments on reader feedback

References:

• Manifesto for Agile Software Development
• The New Methodology
• Scrum - It's About Common Sense
• Surrogate Key vs. Natural Key
• Detecting and Reporting Errors in Stored Procedures - Part 1: SQL Server 2000
• Detecting and Reporting Errors in Stored Procedures - Part 2: SQL Server 2005 TRY-CATCH Blocks
• TSqlTest - The Simple, Lightweight Testing Framework for Microsoft SQL Server

» See All Articles by Columnist Rob Garrison

Join the SQLCredit discussion

SqlCredit - Developing a Complete SQL Server OLTP Database Project