0

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?

2
  • Sounds like personal preference to me. Unless you have some other constraints or issues to add to the question?
    – Dave
    Nov 23, 2015 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, 2015 at 9:11

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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