Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 25, 2007

SqlCredit - Part 5: Adding Card, Vendor, and Purchase, Plus Much Refactoring

By Rob Garrison

Download the code for this article.

This is part 5 of a series. If you have not read part 4, you can find it here.

I am writing this month from Redmond, where I am attending the SQL Server Katmai “System Design Review”. Considering that we’re fairly close to seeing the first publicly-available CTP for Katmai, I have decided to end my struggle with supporting both SQL Server 2000 and SQL Server 2005 in SqlCredit. As of this month, this project supports only SQL Server 2005. Those of you that have already made that plunge with your products are probably thinking “finally”. Those of you that are still required to support SQL Server 2000 in your jobs are probably wishing it were that easy for you to drop support.

As I built out the last pieces of the original table set (Card, Vendor, and Purchase), I found a number of pieces that needed to be fixed in the design. At this early stage in development, allow yourself to rework the design, as you need to, and don’t allow your application developers to lock you into the original just because you gave them the code. They should be dropping and recreating their development database as you publish changes. At some point, we will consider this database “published”. From that point on, changes will have to support upgrades. Until then, nothing is “written in stone”.

New Objects: Card, Vendor, Purchase

I added these new tables and their associated stored procedures. There is also a new script (BuildSqlCredit.bat) that is a simple script to build the SqlCredit database. It currently includes a call to drop the existing database.

These tables and procedures are straightforward. The new schema looks like this.

Not all of the unit tests are included yet. I will add those soon.

Running Changes

PrimaryCardholderID

Based on a previous discussion, I added PrimaryCardholderID to the Account table. The Account record needs to be associated with one cardholder record that is the “primary” cardholder.

This required some creative coding; it borders on “hack”, but it works for now. To insert a new Account takes these steps:

  • BEGIN TRAN
  • Create the Account record with a PrimaryCardholderID of 0 (zero) as a placeholder.
  • Create the Cardholder record for the “primary” cardholder.
  • UPDATE the Account record with the ID of the just-created Cardholder record.
  • COMMIT TRAN

I also had to leave out the foreign key on Account.PrimaryCardholderID. That would have created even more problems. Yuck.

I am not happy with this solution; it will definitely have to be refactored in the future. This will work for now, but I want to see a much more elegant solution. As I have stated before, this series is a process. You don’t see just the final, pristine code.

Small Schema Changes

1. Changed Status column name to StatusCd.

This column name is used in every table and represents a simple name/value mapping (e.g. Account.StatusCd 1 means ‘ACTIVE’). The views do the translation to strings. Because this status value includes this mapping, I have chosen to use a suffix of “Cd” for “code” to differentiate it from other columns. In the views, the string status is returned in the StatusString column. It is consistent throughout the database.

2. Removed the index on Account.StatusCd

All of the indexes are based on experience and assumptions at this point. None of the other tables have indexes on StatusCd, so I dropped the index here. We can add indexes later if we find they are needed.

3. Added “OBJECT::” to DENY statements.

If you’re like me, every time I look something up in SQL 2005 Books Online, I find something new. I was reading through the entry for DENY and saw that they used this “OBJECT::” prefix. It specifies “the class of the securable on which the permission is being denied. The scope qualifier ‘::’ is required.”

4. Added DENY statements to views.

I had DENY statements for the tables but no DENY statements for the views on those tables.

5. Added FILLFACTOR to nonclustered indexes

Each of the clustered primary keys uses an IDENTITY column, so I can leave those fillfactors at the default (100%). Each of the non-clustered indexes will be populated fairly randomly, so I set those to 80%.

6. Renamed filegroups and files for consistency.

I did not like it that I had filegroup names and file names that included “Data”. In general, the naming was inconsistent.

Example Filegroup Changes

Filegroup Name

Old

SqlCredit_FGData1

New

SqlCredit_FG1

Logical File Name

Old

SqlCredit_Primary_Data1

New

SqlCredit_Primary_File1

Old

SqlCredit_FGData1_File1

New

SqlCredit_FG1_File1

Old

SqlCredit_Log1

New

SqlCredit_Log_File1

OS File Name

Old

SqlCredit_Primary_Data1.mdf

New

SqlCredit_Primary_File1.mdf

Old

SqlCredit_FGData1_File1.mdf

New

SqlCredit_FG1_File1.mdf

Old

SqlCredit_Log1.ldf

New

SqlCredit_Log_File1.ldf

I also did not like it that the four non-primary filegroups were each created with two files and the primary filegroup had just one. I added a second filegroup for primary. Now each filegroup and the log have two associated files.

7. Redistributed tables and indexes on filegroups.

The table and index distribution over the four non-primary filegroups was not consistent, so I redistributed everything. This exercise is as much art as science. The basic guidelines are that you don’t want a table and an index on the same filegroup so that the system can do simultaneous writes when a record is added (or updated or deleted). If there are two tables that are often queried together (JOIN), separate those tables onto different filegroups, again so that the system can do simultaneous reads.

Objects and Filegroups:

Object

Table

Index

Filegroup

Account

X

 

SqlCredit_FG1

Cardholder

X  

SqlCredit_FG2

Cardholder.AccountID

 

X

SqlCredit_FG3

Cardholder.LastName

 

X

SqlCredit_FG4

Card

X

 

SqlCredit_FG3

Card.CardholderID

 

X

SqlCredit_FG1

Card.CardNumber

 

X

SqlCredit_FG2

Vendor

X

 

SqlCredit_FG4

Vendor.BusinessName

 

X

SqlCredit_FG2

Purchase

X

 

SqlCredit_FG1

Purchase.CardID

 

X

SqlCredit_FG4

Purchase.VendorID

 

X

SqlCredit_FG3

Filegroups and Objects:

Filegroup

Table

Index

SqlCredit_FG1

Account

Card.CardholderID

 

Purchase

 

SqlCredit_FG2

Cardholder

Card.CardNumber

   

Vendor.BusinessName

SqlCredit_FG3

Card

Cardholder.AccountID

   

Purchase.VendorID

SqlCredit_FG4

Vendor

Cardholder.LastName

 

 

Purchase.CardID

If you look at the distribution of your tables and indexes, they should make sense. For instance, I originally had three indexes on FG3 and one on FG4, so I moved Purchase.CardID to FG4. Some of this may need to be reworked after doing performance testing, but this gives you a good start.

Some of these changes seem fairly minor. What is the point? Consistency. I should have caught some of these as I was writing the initial code. Part of the value I receive from writing this series is ending up with code that I can use as an example in future projects.

Create Boring Schema Code

One thing I notice as I add new tables and procedures to the project: They all look the same. If you looked at Cardholder before and look at Card in the new code, they look painfully similar.

I think of that as a feature, not a lack of creativity. Implement your designs based on best-practices and standards, not on a whim. If someone reads your table-creation schema code, they should be bored. They should not be scratching their head wondering why you chose to name things differently from one table to the next or included DateCreated in one table and not another.

For Next Time

So far, all this personal data is stored clear-text. It needs to be encrypted in the database, and we will tackle that.

Download the code for this article.

Discuss this article in the Forum.

» See All Articles by Columnist Rob Garrison

SqlCredit - Developing a Complete SQL Server OLTP Database Project



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM