1

This is not a programming question, I suppose is a logic question or a database design question.

To answer this you need to know something about bank transactions.

Basically for every transaction (sale, refund,force sale) you make and if the host approves that transaction, the host sends you an RRN (Reference Retrieval Number), this is a number to identify that transaction.

The problem is that I don't know how the host manage this number, I mean I suppose this approach:

The RRN is generated with:

  • Type of transaction
  • RRN
  • Membership (depends of the currency)
  • Number of batch or lot

These four entities could make a UNIQUE KEY in database, the problem is that how do I know when I have to reset the RRN value.

I cannot restrict that UNIQUE KEY in DB, because if you make transactions today, and I don't know if the host reset the RRN value without knowing it I would probably repeat the number of the RRN in the next day and I will throw an error sending that a unique KEY is violated, but that is no the behavior is expected, in theory I don't have to control the RRN number, cause that is a job of the host, but, How do I identify that transaction in the DB if I want to make a cancellation or rolled back that transaction.

I can't have multiple rows if I want to query a unique transaction.

The only thing I have in mind is to identify the transaction with those four entities without making the UNIQUE restriction, but if I query that transaction and I have more than one result, throw an error saying the data is corrupted.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2578332
  • 55
  • 1
  • 4
  • How about having an actually unique ID in your table, which you control yourself - combining this with the four values you receive? Or - if RRN is guaranteed unique at least for every single day - combine the submitted columns with the date and time (including the time should actually safeguard even against RRN reset during the day) to form the unique key? – Abecee Jul 20 '16 at 22:55

2 Answers2

1

If RRN generated not only on DB side is not enough to detect transaction record. It will be issue for the big amount of transactions. RRN can be generated at the Terminal side and in this case your DB is not protected from records duplication at all.

To detect the necessary transaction usually used several additional parameters: Terminal ID (TID), Merchant ID (MID), transaction timestamp at least, etc.

POS integrators may use same TID and MID for many terminals behind and potentially RRNs can be duplicated also.

As already proposed better to use internal DB's unique ID and log everything without restrictions based on KEY for several fields. It is necessary for complains research.

Transactions selection from DB (for Completions, Reversals, or chained Top-Ups) and Duplication detection procedures (for incoming transaction records) should be implemented with possibility to use additional values from the transaction records.

iso8583.info support
  • 2,130
  • 14
  • 18
0

RRN is typically just a host-side counter, such as a database ID "limited" to (iirc) 11 digits. RRN is typically pretty eternal (at least 6 months to ensure refund matching)

What you seem to be talking about is probably systan - value used for request/response matching, it's typically unique in 24h and usually scoped to TID and transmission time fields.

See here a Ruby-based approach to building generic counters which could drive both RRNs and systans

Sequel (Ruby), how to increment and use a DB counter in a safe way?

The solution is probably to keep the hosts RRN as a safe reference to the transaction sent to the host, but maintain your own RRN for your clients and store both in the DB (for example: pos comes, lookup transaction by pos rrn, take acquirer rrn from transaction, send void)

bbozo
  • 7,075
  • 3
  • 30
  • 56