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/