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 Thursday, October 4, 2012 11:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:54 PM
Points: 13,471, Visits: 12,329
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 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 #1368586
Posted Thursday, October 4, 2012 11:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 10:13 AM
Points: 27, Visits: 154
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.
Post #1368598
Posted Thursday, October 4, 2012 11:29 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:27 AM
Points: 34, Visits: 195
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



SQL Tips and Scripts
SQLWorks Blog
Post #1368601
Posted Thursday, October 4, 2012 2:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:55 PM
Points: 6,258, Visits: 7,447
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1368690
Posted Thursday, October 4, 2012 2:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 10:13 AM
Points: 27, Visits: 154
Thanks for all the input! I really appreciate the advice. I'll definitely ask for a meeting with the lawyer. Very good advice.
Post #1368719
Posted Thursday, October 4, 2012 2:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:55 PM
Points: 6,258, Visits: 7,447
Chi Chi Cabron (10/4/2012)
Thanks for all the input! I really appreciate the advice. I'll definitely ask for a meeting with the lawyer. Very good advice.


Excellent. When you're done, do me a favor? Post back here.

Last time I checked the documentation CSC cannot be stored for longer than 30 seconds and never in a permanent storage, only in a variable. If that's changed I'd really like to know it.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1368727
Posted Monday, October 15, 2012 12:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 10:13 AM
Points: 27, Visits: 154
Evil Kraig F (10/4/2012)
Chi Chi Cabron (10/4/2012)
Thanks for all the input! I really appreciate the advice. I'll definitely ask for a meeting with the lawyer. Very good advice.


Excellent. When you're done, do me a favor? Post back here.

Last time I checked the documentation CSC cannot be stored for longer than 30 seconds and never in a permanent storage, only in a variable. If that's changed I'd really like to know it.


Amazing what a little regulation does for requirements! After looking into PCI requirements, I also found that CSC (or other authentication methods) can never be stored. So when I brought this to the attention of the department head and suggested we look into our options with the lawyer, he quickly rescinded that particular requirement. Turns out, the CSC is not required by our CC processing software, that requirement was just put there "just in case we ever needed it."

We can support the other PCI compliance requirements, so when I began going through the PCI self-assessment questionnaire with the department head, he had the brilliant idea that maybe it would be better to have the data entry employees also do the CC processing. That way, we don't have to store ANY CC data, just store the confirmation code from the CC processor.

Of course, that's what I initially suggested. But the up side is that the basic table structure that was my original question does not change, and the security considerations have become a lot more manageable.

Thanks again for all the great input.
Post #1372883
Posted Monday, October 15, 2012 12:36 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:27 AM
Points: 34, Visits: 195
That's great...I have always said that even charging $1 for IT services between departments inside most companies would eliminate stuff like this, in your case the 'cost' was the extra effort and regulatory burden, and it quickly eliminated that which was not needed.
Read Dan Ariely's books on the 'irrationality of FREE' I find it applies directly to software and/or IT departments and the way they interact with their 'customers'
cheers,
-TD





SQL Tips and Scripts
SQLWorks Blog
Post #1372888
Posted Monday, October 15, 2012 12:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:54 PM
Points: 13,471, Visits: 12,329
That is certainly good news. Nothing like having the process happen at the right spot in the business. That self compliance check list has been a real eye opener for a number of people I have worked with.

_______________________________________________________________

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 #1372897
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse