Part 1: Introduction, Business Problem, and Initial Design
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? Whos 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 dont 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.)
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. Lets get started with some initial database schema design and overall design decisions.
Objects to Be Modeled:
- 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)
- 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
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 dont 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 Joys advice and building these triggers for tracking history.
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.
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.
- Real code!
- Database and table creation, initial CRUD procedures (create, read, update, and delete)
- Comments on reader feedback
- 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
Join the SQLCredit discussion
SqlCredit - Developing a Complete SQL Server OLTP Database Project
- 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