October 3, 2012 at 10:56 am
I have a requirement to record payments and payment method information. A payment can be received as ONLY ONE of the following methods: a check, an ACH (telecheck) transaction, or a credit card payment. These three payment methods have different data requirements:
1. Checks only need Payment Amount and Check Number (for our requirements).
2. ACH transactions only need Bank Name, Routing Number and Account Number.
3. Credit Cards need CC#, Expiration Date, Name on Card, CSC, Billing Address
Here is my initial design:
CREATE TABLE [dbo].[Payment](
[PayID] [int] IDENTITY(1,1) NOT NULL,
[PayDescription] [varchar](50) NOT NULL,
[PaymentDate] [datetime] NOT NULL,
[PaymentAmount] [money] NOT NULL,
[CheckNum] [varchar](50) NULL,
[ACHID] [int] NULL,
[CreditCardID] [int] NULL )
The PayDescription is the discriminator to determine where to find the necessary payment information. A check only needs a check number and a payment amount, which is all entered in the Payment table. An ACH transaction will be recorded in the ACH table and referenced in the Payment table as the ACHID foreign key. Likewise, a Credit Card payment will be recorded in the CreditCard table and referenced in the Payment table as the CreditCardID foreign key.
This has worked so far, but it requires so many logic checks and COALESCE statements and constraints to make sure that a PayDescription of 'Check' does not have a ACHID OR CreditCardID entry, etc. So that leads me to think there MUST be a better way.
Does anyone have any suggestions as to the best design for this situation?
October 3, 2012 at 11:07 am
In your example it does not seem possible that a check payment could end up in this table with an ACHID or creditcardID that you would have to check for, are you saying the upstream system that creates these records could enter a check payment with one of these other methods as well? where would that erroneous ID come from?
October 3, 2012 at 1:12 pm
SQLWorks (10/3/2012)
In your example it does not seem possible that a check payment could end up in this table with an ACHID or creditcardID that you would have to check for, are you saying the upstream system that creates these records could enter a check payment with one of these other methods as well? where would that erroneous ID come from?
I'm most concerned about the upstream system during the editing process. A user can enter an ACH payment, then go back and edit the payment and change it to a Credit Card payment--or to a Check payment. So I added a constraint to check that either BOTH the CreditCardID and ACHID columns are NULL (a paper check), or that only ONE of them is NOT NULL (either CC or ACH). This is where I start to think I'm making things difficult.
Another situation that comes up a lot is that the user enters a check payment as an ACH and enters all the routing information and account number (which is printed on the paper check), then someone goes back later to change it back to "Check", at which point the ACH record needs to be deleted. I'd say these are unlikely scenarios, except they happen all the time with our current paper-based system!
Additionally, we may have a client's ACH information on record (a paper authorization form), and the requirements state that ACH data is NOT entered into the database for this circumstance. So we can have a legitimate ACH payment with no associated record in the ACH table. I have addressed this by using two types of ACH payments: "Pre-Auth ACH" and "Telecheck ACH" and using a check constraint to require an ACHID on the "Telecheck" but not the "Pre-Auth".
As I said, what I have in place works, but seems kludgey (especially in my implementation). Without getting too specific on my particular design, what is the best practice for a case like this? It must come up all the time with online vendors. Many have 5-6 different payment methods. How is something like this usually handled? Am I even on the right track? Should I replace the CreditCardID and ACHID columns with a generic "EntityID" column that will refer to either the CreditCardID or ACHID, depending on the PayDescription value (I hope not, that makes my head hurt). Or do I just trust the upstream system code to enforce all the business rules and go with the design I have?
It's the first time I've come across this where I get to design the database. This application replaces a paper-based system and the other system I maintain just uses one giant flat table that contains ALL possible fields--ACH AND Credit Card fields. That sure is readable, but strikes me as poor design (especially when 50% of payments come in as paper checks, so easily 80% of the columns in a record are NULL).
October 3, 2012 at 1:44 pm
I assume its possible that you will receive multiple payments from the same person or company where the only differentiation between records is the payment date, is this correct? Based on that I will give you my take on how to design this, it will take me a little bit to write it up.
October 3, 2012 at 2:18 pm
These three payment methods have different data requirements:
1. Checks only need Payment Amount and Check Number (for our requirements).
2. ACH transactions only need Bank Name, Routing Number and Account Number.
3. Credit Cards need CC#, Expiration Date, Name on Card, CSC, Billing Address
You need to be EXTREMELY careful here. You have some serious issues going on here specifically with credit cards. DO NOT store CC# unless you are very certain your encryption is secure. You need to be PCI compliant for processing electronic credit card transactions. Your structures are in direct conflict with compliance. You are saying you are going to store the CSC. The whole point of that number is for protection with electronic transactions. It is used as secondary check to ensure that the card is present during the transaction. How certain are you that your data is encrypted during transit AND at rest? You are storing credit card data and bank account numbers. DO NOT take this lightly. The fines and penalties for mishandling this type of stuff can be staggering.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2012 at 3:16 pm
Sean Lange (10/3/2012)
These three payment methods have different data requirements:
1. Checks only need Payment Amount and Check Number (for our requirements).
2. ACH transactions only need Bank Name, Routing Number and Account Number.
3. Credit Cards need CC#, Expiration Date, Name on Card, CSC, Billing Address
You need to be EXTREMELY careful here. You have some serious issues going on here specifically with credit cards. DO NOT store CC# unless you are very certain your encryption is secure. You need to be PCI compliant for processing electronic credit card transactions. Your structures are in direct conflict with compliance. You are saying you are going to store the CSC. The whole point of that number is for protection with electronic transactions. It is used as secondary check to ensure that the card is present during the transaction. How certain are you that your data is encrypted during transit AND at rest? You are storing credit card data and bank account numbers. DO NOT take this lightly. The fines and penalties for mishandling this type of stuff can be staggering.
Thanks, I will definitely look into this. I have been concerned with this requirement to store CC data, myself. As it is, the application is an intranet-only application, not available outside the LAN. It uses SSL over our IPSEC infrastructure and the application applies triple-DES encryption to the data before it is stored in our database. I'm the paranoid sort, so I have planned to encrypt that encrypted input using column encryption. This storage is strictly temporary and used ONLY as a means to transport the CC info from the data entry users to the accounting users who will actually enter the CC data into the CC processing software from our bank (we get very few CC payments--currently less than 2%). Once the data has been entered into the CC processing software, it is removed from the Credit Card table.
Hopefully that covers me but I will definitely scrutinize those regulations. Thanks for the heads up
October 3, 2012 at 3:19 pm
SQLWorks (10/3/2012)
I assume its possible that you will receive multiple payments from the same person or company where the only differentiation between records is the payment date, is this correct? Based on that I will give you my take on how to design this, it will take me a little bit to write it up.
That is correct, we can receive many payments from the same individual in many different formats. Thanks for your help!
October 3, 2012 at 6:20 pm
I put some time into thinking through this design, and I am realizing that the right question to be asking here is probably more along the lines of "Why am I storing this data at all?" instead of looking for the best way to store it, given the sensitive nature of the data. Are there users who will be looking at this after each transaction is completed? why are you even taking possession of a credit card number at all, after the customer swipes their card you shouldn't need to retain that information I wouldn't think, and I would say keeping it in any form presents more risks than not. Will this data be queried later on, and in what way? by individual transaction, or in aggregate?
I understand it may not be your place to question the requirements you were given, but I think at least a little push back is warranted...it just seems strange that you would want or need to retain this kind of data about your customers.
I've worked at a bank for 12 years and have seen the security requirements evolve from barely existent, to on site Treasury department audits occurring every 90 days (its a big bank), so now when someone tells me they need a list of account numbers I make sure they have a damn good reason.
October 4, 2012 at 10:36 am
SQLWorks (10/3/2012)
I put some time into thinking through this design, and I am realizing that the right question to be asking here is probably more along the lines of "Why am I storing this data at all?" instead of looking for the best way to store it, given the sensitive nature of the data. Are there users who will be looking at this after each transaction is completed? why are you even taking possession of a credit card number at all, after the customer swipes their card you shouldn't need to retain that information I wouldn't think, and I would say keeping it in any form presents more risks than not. Will this data be queried later on, and in what way? by individual transaction, or in aggregate?I understand it may not be your place to question the requirements you were given, but I think at least a little push back is warranted...it just seems strange that you would want or need to retain this kind of data about your customers.
I've worked at a bank for 12 years and have seen the security requirements evolve from barely existent, to on site Treasury department audits occurring every 90 days (its a big bank), so now when someone tells me they need a list of account numbers I make sure they have a damn good reason.
It all goes back to a work flow requirement. The users talking to the customers and entering the payment information are not the ones actually processing the payments--Accounting does that. I need some way of storing that data just long enough to alert Accounting of a new payment and for them to go in and grab it and process it on the third-party software. Once the data is submitted to the ACH or CC processing software, it is deleted from our database. The end user does not have the option of overriding the deletion. Given those work flow requirements, I can't think of any other method than actually storing the sensitive data on a short term basis (typically less than 24 hours).
Right now the front-end users are taking down the information and sending it to Accounting in an email! That is the procedure we are trying to secure and replace.
Any way of NOT storing this data would be wonderful news to me, but I don't know of any other way to get the required data from the person who takes the data to the person who uses the data.....
October 4, 2012 at 10:39 am
So the people talking to the customers and taking down the payment info, how would they get the information into the database? Is there a GUI or some kind of software they use that write back to it, or do they enter it using SQL scripts themselves?
Of course, the best answer is to just let the people talking to customers enter the payments...this would also save you some money in accounting salaries...
October 4, 2012 at 11:00 am
I agree this process sounds like it was not well designed. How do you handle things like credit card rejections or simply typing in bad info? By that point the customer is long gone and expecting their product but you can't process the payment. As a customer if somebody called me back the next day to tell me my card didn't go through I would be very suspect and start asking all sorts of questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2012 at 11:19 am
SQLWorks (10/4/2012)
So the people talking to the customers and taking down the payment info, how would they get the information into the database? Is there a GUI or some kind of software they use that write back to it, or do they enter it using SQL scripts themselves?Of course, the best answer is to just let the people talking to customers enter the payments...this would also save you some money in accounting salaries...
Hey, I've got all KINDS of great ideas that would save the company a FORTUNE, but what do I know? 🙂
Yes, the data entry users have a GUI where they enter that data. It's an ASP.NET application that runs on our intranet.
October 4, 2012 at 11:29 am
So you will be reworking the asp page to use whatever new design you come up with then? From my initial look at it, it will be pretty drastically different than what you have now, so the asp will have to account for that
October 4, 2012 at 2:04 pm
Hey Chi Chi,
I've done something similar to this, and Sean's misgivings notwithstanding I agree there are times workflow needs this information.
However, make sure that accounting has a way to go back to the data and remove the CSC information from the CC data, or you're in for an audit from hell eventually. Also I would only recommend storing an encrypted version of the CC# with the key(s) at the ASP end so that only a single value can be retrieved at a time. This will save you in case someone gets disgruntled with access to a system that noone really thought was a problem at the time.
So, to the construction of the build... Short form, what you're doing, while kludgey with the constraint, is pretty much the only way to approach this. One thing I would add is including the pre-auth ACH into the ACH table and using it as a multi-reference for all pre-auth transactions for a single customer, simply for tracking.
I would also, personally, always include a 'transaction amount' for every transaction. The reason being that you'll probably want to be able to have one place to easily sum up information for a particular client/customer. The presense of the descriminator and a check number will indicate if it's a hard check or not with this value in place.
Your approach is sound, what you're basically doing is creating an 'overview' table that combines all the different types into a single place to review the data. These usually get a bit finicky.
The only other thing I would recommend here is a reiteration of what's been said, get your hands on a copy of the PCI documents and request a 1 hour session with legal about necessary storage requirements from them. You'll get a bit of push back from management, but stand your ground. Two reasons. First, it's REALLY good to know these rules from a lawyer directly. We can stand on our heads and tell you it but really, you want your company lawyers to sign off on what the rules are, because they're the only official fallback you'll have during an audit. Second, it's just good to know for sure what the rules are in the first place, for the next job, and the one after that. PCI isn't going away, and if you work in e-commerce you really want that information in your toolkit.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 4, 2012 at 2:37 pm
Thanks for all the input! I really appreciate the advice. I'll definitely ask for a meeting with the lawyer. Very good advice.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply