Say you have 8 different data models that are related enough to share roughly 70% of the same fields, but the shared fields are not always the same. And also within any given model, some fields can be empty. The business logic is that data is pulled from all the data models and put into a common format that has all the combined fields, and sent over the wire as a transaction.
The models are different credit card transaction types. Most of them share a lot of common fields, but each one is different by about 3-6 fields, and each one has different requirements on which fields can be empty and what their default values are. In the past I've basically used one table and modeled it after the message format that is sent to the processor. I'm thinking about creating a table for each transaction type though to cut down on the number of empty fields for each transaction type, and also to limit the number of options that is allowed in each field. For example across the transaction types credit, capture, authorize, force, charge, and voice authorize the authorization_source_code field might have 8 possible values, but in any one transaction type it probably only has 3 at the very most. Would most of you create a separate tabel for each transaction type? I'm also curious how others would handle the batch closings. In the past I have created separate tables for open transactions and transactions that have been captured/settled. When a transaction is captured it's moved to a different table instead of just having a column to mark it as captured. Normally I would select all the transactions to capture, insert them into the captured table, delete them from the open transactions table, process the batch, and if the batch goes through commit everything. That narrows down the number of things that can go wrong after you have submitted the batch. The alternative would be to just have a column to mark transactions as capture and leave them all in one table. I've always been paranoid about doing that because it leaves open the possibility of capturing thousands of transactions twice if you have a bug, as opposed to a few hundred at most. I spent quite a few years working at payment gateways and am now creating an open source platform that does the same thing that your normal payment gateway does. It's been a while since I've had the chance to look at this problem in a fresh light. Most of the processing code at payment gateways is left pretty much untouched once it's working, it's not something you go in and refactor every few months even if it's not perfect. Would appreciate any feedback. Chris ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
