SqlCredit - Developing a Complete SQL Server OLTP Database Project

Monday Jan 29th 2007 by Rob Garrison

The SqlCredit series demonstrates the database design and development portion of a complete, functioning, tested credit card database to illustrate the complete software development lifecycle.

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


» See All Articles by Columnist Rob Garrison

Join the SQLCredit discussion

SqlCredit - Developing a Complete SQL Server OLTP Database Project

- Performance Testing SQL 2008's Transparent Data Encryption
- SQL Server 2008's Change Data Capture - Tracking the Moving Parts
- Performance Testing - SQL Server 2008 versus SQL Server 2005
- Exploring SQL Server's Index INCLUDEs
- Exploring SQL 2005's Ranking Functions - NTILE() and ROW_NUMBER()
- SqlCredit - Part 19: Exploring SQL 2005's Ranking Functions - RANK() and DENSE_RANK()
- SqlCredit, Part 18: Exploring the Performance of SQL 2005's OUTPUT Clause
- SqlCredit - Part 17: Exploring SQL 2005's OUTPUT Clause
- SqlCredit - Part 16: The Cost of Bloat
- SqlCredit - Part 15: The Cost of Distribution
- SqlCredit - Part 14: The Cost of Translation
- SqlCredit - Part 13: More on Indexed Persisted Computed Columns
- SqlCredit - Part 12: Exploring Indexed Persisted Computed Columns
- SqlCredit - Part 11: Change Tracking Using History Records
- SqlCredit - Part 10: MAC Performance and Updating SqlCredit
- SqlCredit - Part 9: Message Authentication Codes
- SqlCredit Part 8: Comparing Encrypt/DecryptByCert and Encrypt/DecryptByKey
- SqlCredit Part 7: Performance Impact of EncryptByCert and DecryptByCert
- SqlCredit Part 6: Exploring EncryptByCert and DecryptByCert
- SqlCredit - Part 5: Adding Card, Vendor, and Purchase, Plus Much Refactoring
- SqlCredit - Part 4: Schema and Procedure Security
- SqlCredit - Part 3: Schema/Proc Updates and Automated Unit Testing
- SqlCredit - Part 2: Creating the Database, Tables, CRUD Procedures
- SqlCredit - Developing a Complete SQL Server OLTP Database Project

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved