This is part 3 of a series. If you have not read part 2, you can find it here.
In this months installment, we will discuss updates based on feedback from part 2 as well as introduce automated unit testing.
As stated in part 1, This series will be about the process that results in a well-designed, well-coded, and well-tested database. Part of that process is updating the design and code based on feedback from other members of the team. In this case, the feedback generally comes from the forum.
Stored Procedure Naming Convention
I got strong feedback in the stored procedures thread that <object><action> naming is better than <action><object>, so I changed the naming convention.
Account and Cardholder Tables
Based on forum feedback on CardType and AccountType, I will add type (Gold, Platinum, etc.) to Account instead of Card. I originally planned to call this CardType, but that no longer seems appropriate. I will have to call it something other than type because that is not descriptive enough. If you have naming suggestions, pass them on. I know this is a fairly minor decision, but it is something typically discussed with other developers in a group.
The Gold/Platinum change caused me to challenge the placement of the Partner (Alaska Airlines, Lexus, etc.). That also looks like it needs to be part of the account and not the individual card.
If a person has more than one type or partner (a Gold card from Lexus and a Platinum card from Alaska Airlines), then those cards will be part of separate Accounts. That persons personal data will be duplicated.
These changes will have to wait until the initial version of the tables is complete and tested.
I had requests for an E/R diagram. This would definitely be helpful. In a normal project, I make a point of creating diagrams and passing them out to the developers. My goal is to see people using the E/R diagrams as a reference throughout the project. Here is a logical diagram of the currently-implemented schema:
DateTime and UTC
We had an interesting discussion about datetime columns and UTC. The datetime columns will remain UTC as they were originally.
Also, I found that using smalldatetime for DateCreated and DateLastUpdated made unit testing difficult. The precision is just not good enough for unit testing or, most likely, for production. I changed the columns to a full datetime.
I had set a number of columns to be nullable (Account.Address2, Cardholder.FirstName, etc.). I really dont want to have to write IF <col> IS NULL OR <col> = ''code for these simple string values, so I changed those to be NOT NULL. I will leave it to the calling code to check for empty strings in places where a value is required.
Somehow, I had named the three phone numbers HomePhone, MobilePhone, and WorkNumber. I fixed it by changing WorkNumber to WorkPhone.
There was not as much forum discussion of database options as I had hoped, but I have done more reading based on prompting from Scott Hanselman and other regional directors. (See the References and Further Reading section at the end.) I made two updates to CreateDatabase.sql based on this feedback. Specifically, I added SET statements for ANSI_PADDING and NUMERIC_ROUNDABORT.
I am not at all satisfied with this one. I will continue to do my own research, and I have a few people helping me. I am hoping that I will get more feedback from readers in the forum.
CRUD Procedures for Account and Cardholder
The Account and Cardholder tables now have a complete set of CRUD stored procedures. The procedures include the updates to the tables. These are available in the attached code. Other Read stored procedures may be added later, but the base procedure is there now.
Automated Unit Testing Using TSqlTest
I wrote and published TSqlTest out of my own need to have an automated set of unit tests for stored procedures. It is written specifically for Microsoft SQL Server. There are options, most notably TSQLUnit and the recently-published Visual Studio Team Edition for Database Professionals.
What is TSqlTest and How is it Different?
TSqlTest is a simple, lightweight database test framework. It utilizes .sql and .bat scripts only. No perl, no VB, no ruby; just T-SQL code wrapped in a bit of batch file. It is designed to be used for automated testing and incorporated into a continuous integration environment.
It supports testing of stored procedures, functions, triggers, and database state. It can test pretty much anything written in T-SQL. I suspect it will be used for parts I havent even thought of yet.
Here is a diagram of the TSqlTest tables:
and an example of a test command and the output from the test run in the attached code:
>.\Tests .\RGINST1 full =============================================== Results from tests on database SqlCredit: Total Tests: 23 Failed Tests: 0 Open Defects: 0 Failed Files: 0 ===============================================
- Automated reporting (human-readable and XML/Nant output)
- Support for known defects
- Report results even if a script fails
- Scripts include before/after counters for tables (verify that the tests did not leave rows behind)
Testing Database State
TSqlTest allows you to write simple tests of database state to verify a certain level of goodness in your database before firing off the rest of the tests. Examples include testing for tables, indexes, stored procedures, functions, defaults, and data.
By convention, I always create a test scripts called _InitialDatabaseStateTests.sql for each database. The attached script tests for
- the existence of the TSqlTest framework
- the existence of the Account table
- the existence of the CreateAccount stored procedure
- the default for the Account.DateCreated column (making sure it is set to GETUTCDATE())
This script is great for adding simple tests as a database evolves over time. If you add a column to an existing table, add a test here that validates the existence of the column. If you change a default, add a test that validates the new default.
I have created a forum thread to discuss TSqlTest and database unit testing in general.
For Next Time
More testing, more running changes based on feedback, and ... finally ... security.
Download the code for this article.
References and Further Reading
- SET Options That Affect Results at MSDN
- Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 at TechNet
- Stored procedure recompiles and SET options by SQL Server MVP Tibor Karaszi
- SqlCredit - Part 1: Introduction, Business Problem, and Initial Design at Database Journal
- SqlCredit - Part 2: Creating the Database, Tables, CRUD Procedures at Database Journal
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