How To: Maintain Customer Payment History
Creating and maintaining ‘customer’ data is a common task that most people handle by creating a single table that holds a customer’s FirstName, LastName, Address, City, State, Zip etc. But when it comes to tracking changes in a customer record this type of table fails to provide needed functionality. This article will show how to maintain a customer’s basic data (name, address etc.) and also keep track of transient data such as an ‘IsActive’ status that is derived from whether or not the customer has paid for service within the last 30 days.
Since we will also want to keep a complete history of the customer’s payments, we can’t just add a single column to our table called ‘IsActive’. (Also, as a side point, proper naming conventions will help you to understand that if a column or property name starts with the word “Is” then you know right away that this is a derived or calculated value.) In our case, we are going to create a VIEW that will include all of the customer’s basic data and also a field indicating that the customer has paid within the past 30 days. To accomplish this, we will need to simple TABLEs.
First – The Customers TABLE
To start off easy, we are going to create a table to hold all of our customer data. We will call this table ‘Customers’. This table will contain an ID field of our customer as well as his name and address information. Our ID field will be the PRIMARY KEY for this table, and for now it can also be the CLUSTERED INDEX as well. (This is the default behavior if you are using the SQL Server 2000 or SQL Server 2005 IDE and you click the “Primary Key” button.) Here is our Customers table definition:
(
ID INT NOT NULL IDENTITY (1, 1),
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
Address VARCHAR(50) NULL,
City VARCHAR(50) NULL,
State CHAR(2) NULL,
Zip CHAR(5) NULL
)
ALTER TABLE dbo.Customers ADD CONSTRAINT
PK_Customers PRIMARY KEY CLUSTERED ( ID )
Second – The PaymentHistory TABLE
Now that we have our simple Customers table, we are going to create they PaymentHistory table. This will keep track of all payments for all customers. The fields we are going to need are an ID field for each PaymentHistory record, the CustomerID to tie back to the customer and a DateTime field to let us know when the payment was made. Here is the SQL statment to create the PaymentHistory table.
(
ID int NOT NULL IDENTITY (1, 1),
CustomerID int NOT NULL,
PaymentDate datetime NOT NULL DEFAULT GETDATE()
)
ALTER TABLE dbo.PaymentHistory ADD CONSTRAINT
PK_PaymentHistory PRIMARY KEY NONCLUSTERED ( ID )
CREATE CLUSTERED INDEX IX_PaymentHistory_CLUSTERED
ON dbo.PaymentHistory ( CustomerID, ID )
Joining The Tables Together
This is the PaymentHistory table that will be used to track when a customer has payed his bill, and will therefore have another 30 days of service. Notice that we didn’t use the default clustered index on the pimary key column for this table. The reason for this is that we will be joining to this table using the CustomerID field. By clustering this table first on CustomerID then on ID we will gain significant performance boosts when running joining or running aggregates using CustomerID as a parameter. We also chose to add the ID column as the second part of the clustered index so that our events will sort in order of payment history by default. This technique is discussed in another article on SingingEels.com – “SQL Performance – Clustered Indexes”.
What remains is for us to create a view that ties the two tables together. Our goal is to have all the customer information plus we want to know if the customer has paid within the last 30 days which indicates his being ‘active’. There are a few good ways to do this; we will look at 2 options and discuss the differences.
Our first method of tying the data together won’t involve using a JOIN statement. What we are going to do is simply select all the Customer data and add one column that will be the most recent payment date for the particular client. This will be accomplished using the MAX function to get the latest date and using a reference to the “parent” table (which in this case would be the Customers table) to ensure we only get the latest record for the specific customer in each record.
*,
(SELECT MAX(PaymentDate) FROM dbo.PaymentHistory
WHERE CustomerID = Customers.ID) AS LastPaymentDate
FROM
dbo.Customers
This method above is fast and easy to do. The problem here comes in when you want more than one piece of data from the PaymentHistory table. To do that, you wouldn’t want to do another sub-query to the same table as this is now adding needless processing on the SQL side. Instead, to pull back multiple fields from the PaymentHisotry table we are going to use a JOIN. Keep in mind though that we don’t want all the records for each customer, but rather all we want is the latest record. This slight modification to the code above will allow us to get the last payment date as well as the ID of the payment record.
Customers.*,
LatestPayments.*
FROM
dbo.Customers LEFT OUTER JOIN
(SELECT CustomerID, MAX(ID) AS LastPaymentID, MAX(PaymentDate) AS LastPaymentDate
FROM dbo.PaymentHistory GROUP BY CustomerID) LatestPayments
ON Customers.ID = LatestPayments.CustomerID
In order to get the latest payment data for each customer we made an inline sub-query inside of our JOIN clause. This subquery basically acts just like a VIEW. In fact, if you made that query into a VIEW and called it LatestPayments, then you would be doing the exact same thing that SQL Server is doing in the background. What remains now is to use this logic to make our “IsActive” column based on whether or not the customer has made a payment within the past 30 days. This will be done using a CASE statement.
*,
CASE WHEN ((SELECT MAX(PaymentDate) FROM dbo.PaymentHistory
WHERE CustomerID = Customers.ID) >= (GETDATE() – 30))
THEN 1 ELSE 0 END AS IsActive
FROM
dbo.Customers
This query above reads this way in english: “Get all the records from the customers table, and when the latest payment for the customer is less than 30 days old, then the customer is active, otherwise he is inactive.” Now you can make this query into a view and display it on a customer management page on your web application. You could also use this view to dis-allow users from accessing certain pages due to no longer being an active member of your site.
This pattern is easy to follow and it gives much flexibility with minimal code. You could adapt this to work with a “case management” solution where you need to track case status changes, a customer payment system or many other scenarios.
Source:singingeels.com
Comments