Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Design Question for Discriminator Column Expand / Collapse
Author
Message
Posted Wednesday, October 3, 2012 10:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:52 AM
Points: 27, Visits: 153
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?
Post #1367863
Posted Wednesday, October 3, 2012 11:07 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:17 AM
Points: 34, Visits: 194
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?




SQL Tips and Scripts
SQLWorks Blog
Post #1367869
Posted Wednesday, October 3, 2012 1:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:52 AM
Points: 27, Visits: 153
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).

Post #1367989
Posted Wednesday, October 3, 2012 1:44 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:17 AM
Points: 34, Visits: 194
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.





SQL Tips and Scripts
SQLWorks Blog
Post #1368011
Posted Wednesday, October 3, 2012 2:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 13,139, Visits: 11,980

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1368027
Posted Wednesday, October 3, 2012 3:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:52 AM
Points: 27, Visits: 153
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
Post #1368059
Posted Wednesday, October 3, 2012 3:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:52 AM
Points: 27, Visits: 153
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!
Post #1368060
Posted Wednesday, October 3, 2012 6:20 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:17 AM
Points: 34, Visits: 194
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.




SQL Tips and Scripts
SQLWorks Blog
Post #1368124
Posted Thursday, October 4, 2012 10:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:52 AM
Points: 27, Visits: 153
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.....
Post #1368572
Posted Thursday, October 4, 2012 10:39 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:17 AM
Points: 34, Visits: 194
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...




SQL Tips and Scripts
SQLWorks Blog
Post #1368574
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse