SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design Question for Discriminator Column


Design Question for Discriminator Column

Author
Message
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63508 Visits: 17966
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.

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)
Chi Chi Cabron
Chi Chi Cabron
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 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.
SQLWorks
SQLWorks
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20983 Visits: 7660
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
Chi Chi Cabron
Chi Chi Cabron
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 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.
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20983 Visits: 7660
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
Chi Chi Cabron
Chi Chi Cabron
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 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.
SQLWorks
SQLWorks
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63508 Visits: 17966
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search