High level transactions with states in a double entry accounting system

Question

Assume there is a system with Double-Entry accounting:

• https://stackoverflow.com/a/59465148/4896540
• https://stackoverflow.com/a/59857806/4896540

I prefer the latter model with normalised Transaction.

There are long running complex operations with many states. One large transaction affects many ext. accounts (or even many ledgers), you can reverse (post opposite Transactions), add new transactions aka Fee, Penalty or redistribute money from all involved ext. account/ledgers transactions when state changes. Must keep references to Transactions and don't duplicate Amount in these process-specific tables.

More examples:

The simple example of ApplicationTransaction is a Bet which consist of several Pledges when you take some collateral from each participant. Each participant even has different assets at stake and House can take many of them to satisfy some requirement. I am thinking about generic ApplicationTransaction table with Discriminator and many specific tables.

And ApplicationTransaction table with State column referencing many double-entry transactions it performs. During lifetime of the ApplicationTransaction it could post (make Transactions) on its state changes, but not always. For example Bet takes collateral and releases it when Bet time is up, under some circumstances it redistributes initial amounts this operation held, but some of its states does not post.

A Lottery (which is the most made up use-case here) could be an example of ApplicationTransaction which affects many accounts, it starts and ends with massive "airdrop" of winnings. Each instance has its own attribute values, the properties are static.

Another use case is Trade between two ext. accounts where House can be a middleman, must take assets from each side, move it to special LedgerAccount|XYZ|AL|Escrow|, one per ApplicationTransaction.Type, not per instance. Keep a record of transfers related to specific Trade instance, could last for a while, have several states, attributes, different outcomes for ext. account holders which could be a penalty to one side and repayment to another. Without orderbook or matching engine. Such exchange process has several states, another counterparty could be involved in a dispute resolution if such State transition happens. Both participants must flag a Trade with something like Payment Received (assuming the payment is done outside of the system). That's a state transition. System could charge fee from each participant.

It's not a single Transaction entry. A group of them. For instance if I need to put some amount into escrow I can put X * Emeralds and X * Diamonds in order to date a princess. So it not only posts many (AssetType, Amount, AccountNo) (N * assets * 2 parties) but also posts into Income LedgerAccount. Assume we perform a trade over a bucket of Assets.

Could also add some table which restricts possible transitions per operation type so we can add constraints. I see it as a copy of State lookup table with extra NextState column references itself, this way it defines a set of available states. Out of scope of this question though.

I am obviously not solving a new problem here, so the question is what is obviously wrong with my design and how correct direction would look like.

Perhaps there is still misunderstanding from my side, but I don't think that each application use-case instance should produce disposable LedgerAccounts, because we don't create new HouseCash per use-case Deposit and Withdrawal. The Deposit/Withdrawal is also fits into ApplicationTransaction category, with few state transitions (like rejected by payment processor). In a real app you have a special tables to handle it, there is a payment method and amount, etc.

Otherwise such approach will result in hundreds of thousands of disposable LedgerAccounts. The question is not a request to made a general purpose model of general purpose system for Universe, it's a question whether the direction is right, Not sure that we have to keep one-time accounts, as I already have stated, I see Account and LedgerAccount as something required to derive House reports, maintain customer accounts otherwise it looks as stupid as defining account per CPU-core on AWS EC2.

Answer 1

Reference Data Model

I prefer the latter model with normalised Transaction.

In keeping with SO guidelines, each Answer is limited to the Question. The data model in the first Answer satisfies the Question, and assumes an understanding of the Ledger. The second question assumes no understanding of the Ledger, so the second Answer gives a full explanation of the Ledger, and requires an even more detailed data model.

By all means, we will use the second data model.

---

Problem • Approach

There are long running complex operations with many states. One large transaction affects many ext. accounts (or even many ledgers),
_{where you can rollback, add new transactions aka Fee, Penalty or redistribute money from all involved ext. account/ledgers transactions when state changes.}

Definitely, absolutely, not. Please forget about thinking in IT or CS terms. Think only in Accounting terms (and later, at implementation, implement the Accounting requirements).

• Each Accounting or business transaction is single, immediate. It involves one Ledger Account Number LedgerNo on one side, and either another LedgerNo or an External Account Number AccountNo on the other side.

• There is no waiting, no states, no progression.

• There is no such thing as a business or Accounting transaction that affects "many external Accounts" (or many other Ledger Accounts). Such a perception is not an Accounting one.

  • If you implement anything along those lines you will not have an Accounting system, you will have an Anti-Accounting system (with or without DEA), that fails Bookkeeping, fails Accounting, and fails Audit.

• You can have a procedure that affects many Accounts (Ledger-Ledger or Ledger-[External]Account). But that procedure executes single business transactions. Examples in the other Answer are:

  • § 5/2 Charge Monthly Fee (pseudo-code)
    Imagine a BEGIN TRAN/COMMIT TRAN bracketing that INSERT
  • § 6.5. SQL Batch Task • Account Month End
    It specifically advises SQL Transactions to be executed in batches. That means a `COMMIT TRAN/BEGIN TRAN every 100 or 200 business transactions. Usually one would have restart control, etc. (If you do not understand that, please ask.)

I am thinking about generic Operation table with OperationType Discriminator and many specific tables for each Operation.Type.

(No comment on the Operation table.)

An ordinary Exclusive Subtype cluster in Relational or IDEF1X terms.

... so we can add constraints. I am not sure where its place though, perhaps application code.

• Never place constraints or anything that constrains the logic (Consistency) of the data anywhere other than in the database.

• The database is a single recovery unit. It must contain

  • all constraints (there is no logical limit; anything and everything in a Relational database can be declared in terms of FOPC Predicates), and
  • all Transactions, in stored procs.

• All tables are GRANTED SELECT permission only, never GRANTED INSERT, UPDATE, DELETE. That means no direct writes to tables.

• The Transactions (list of stored procs) are the Database API. GRANT EXEC permission to the carefully chosen Roles, and thus to specific Users.

• All app code, which in either in the client, or in some middleware tier, executes Transactions only. Still only by permitted Users

Otherwise, you do not have a database, you will have an unsecured data mess. Refer to Open Architecture Standards. (That is the simple definition, for public consumption.)

---

Problem • Application

The simple example of Operation is a Bet which consist of several Pledges when you take some collateral from each participant. Each participant even has different assets at stake and House can take many of them to satisfy some requirement.

These directions are based on implementing an Accounting System, with Double-Entry on every Accounting transaction, wherein no money (or asset) get lost, and any discrepancy can be easily traced ... which makes it Audit-able.

1. Each betting person will have a separate external Account.

2. There will be an AssetType table that defines the different types of asset, what you call "collateral". Think in terms of "money" in different "currencies" (hence DEA is feasible).

3. The Account will be qualified by AssetType, giving the AccountAsset table. AccountAsset (and not Account) will be transacted against. We are transacting assets per AccountAsset, not money, not the overall value in the Account.

4. On the Ledger side, first there will be a Suspense or Pending Account.

5. Next, What you call a "state" is an entry in the Ledger under Suspense. But then, even your notion of "state" has to be tightened up to conform to the notion of a Suspense or Pending Account. Therefore I cannot use your examples directly, I will give what I can determine (feel free to clarify or add more). Tentatively, I will call this SuspenseState. The Values are:

   • Open Bet (pending, not closed)

   • Insufficient Funds (Bet closed, asset not collected)

     • There is a further refinement, to ensure that that is prevented. After the SuspenseStates have been correctly determined, and not before, this can be discussed.

6. Next, under each SuspenseState, there will be one entry per AssetType. These are LedgerAccounts, transacting against all external Accounts. [4][5] are not transactional, they are aggregate LedgerIntermediates.

Your Data Model

Great graphic, by the way. And thank you for expressing your question clearly, in data model (graphical) terms.

I have answered above per the requirements that I have quoted, which I understand somewhat. I cannot see how that relates to the data model (requirement: collateral vs data model: lottery). And I cannot make sense of what Operation is doing. You are deeply into the how to do what you need, but we do not yet understand what we need. The order is, first define the what, then define the how to.

1. Please explain in technical terms in English (edit your question), the Lottery & Trade (I understand that that is equivalent to transferring money between external bank Accounts ... but why the delay, why not immediate ?).

2. Please explain what each of the "states" mean (one sentence identifying the action taken, and each side):

   • AAA: New; Completed; Cancelled

   • BBB: Pending; Conflict; Resolved

   • CCC: New; Complete; Rollback

3. Why does your model not have "collateral" types (my AssetType) ?

4. Caveat. Stamping an ID field on every file will severely cripple the modelling exercise. Why ? Because you are assuming that the file is correct, but it is not. You are fixing the "entity", and the "entity" has not been modelled yet. The purpose of modelling is to model the data, only the data, and nothing but the data (the ID field is not data, but an addition) ... such that the "entities" are exposed, distilled, determined.

   Therefore, in order to do some genuine data modelling, remove the ID fields. Which means, you have to choose a logical Identifier for each corrected file, which action will elevate it to the status of a table. The Relational Model demands that the Key is "made up from the data".

   If ID fields remain, there are further caveats, which I won't detail, because if they are removed, the caveats too, will disappear. (These are common problems. If interested, you can read some of my other Answers, wherein I detail each problem and give the solution.)