A Bank B sends a set of transactions to Bank C every few minutes. Same transactions could be sent multiple times (Bank C should recognize those transactions and should not process them).

At the end of the day, Bank B sends a reconciliation file (that SHOULD contain all transactions that SHOULD BE processed during the day) to Bank C.

Reconciliation process should provide a report with missing information for set of transaction during the day comparing with the set at the end of the day. Beside that, if any missing transaction is found, it should be processed.

Should the system use one table for transactions and another one for reconciliation or one single table for both information since the structure of data is the same?

  • Sounds like personal preference to me. Unless you have some other constraints or issues to add to the question? – Dave Nov 23 '15 at 20:05
  • @Dave, From my side both solutions look very similar. I would like to know from a dba perspective. Which is better to manage!? More efficient? More flexible to accommodate changes (we can imagine that new requires are coming; probably someone wants to know how good/reliable is the data transfer during the day; and so on). – rdllopes Nov 24 '15 at 9:11

It depends on amount of data and how it will be used. You can have one table for with flag for intraday/COB datasets, then queries that would filter on those flags. After that you'll probably use EXCEPT to get unprocessed transactions. Then you need to keep this data for 2-5-7 years. There may be more requirements. It is OK if you have enough capacity and the only use for this table is one-off reconciliation and then storing data.

However, if you need to run reports on COB data, join it to dimensions, do more reconciliation, enrich with more attributes, send to downstream systems, then you would go for a separate table.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.