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.