Level 102
Have you considered looking into event sourcing?
https://spatie.be/docs/laravel-event-sourcing/v7/introduction
1 like
Jan 27, 2022
5
Level 51
Best Practice for Database Scheme for Accounts/Transactions...
context: I am writing a trading algo. I want to manage transactions and account balances for various trading accounts (and brokerages). The transactions relate to trades and balance changes, fees, starting balances etc.
Poking around on the web for database schemes...
transactions
-document_id // I assume this is for a check or paperwork that's related.
-description
-date
ledger_entries
-transaction_id
-account_id
... amount and so on.
accounts
...
So there isn't a balance stored? And why not combine the ledger and the transactions? What is the best way to manage this? Is there a typical or best practice here?
Level 51
@Sinnbeck, um not until just now ;)
I found a great article on the topic.
Indeed one way of thinking about accounts is that the Accounting Entrys are a log of all of the events that change the value of an Account, so that an Account is itself an example of Event Sourcing.
Level 102
@webrobert cool. Spatie also has a full course (book) on the subject. Haven't bought it but their quality is usually good
Level 51
@Sinnbeck, Thank you for this. I'm still swimming a bit, mentally. Reading their docs. But I am excited about this. One of the things I've been considering is to keep state on a stock, say the last 20 bars. So the App, or Aggregate perhaps, is always aware of what has happened with that particular stock and is ready to act on it. Like I would if I were watching it and following along.
Level 80
@webrobert Yes, you’d have a transactions table. If this is for accounts, then you would record debits (subtractions) and credits (additions) to an account. At it’s simplest:
Schema::create('transactions', function (Blueprint $table) {
$table->id();
$table->foreignId('account_id')->constrained();
$table->integer('amount');
$table->timestamps();
});
So now, for any account, you can record debits and credits. A credit would be a positive value in the amount column and a debit would be a negative value. The balance of the account is just a SUM of amounts:
$balance = Transactions::where('account_id', '=', $accountId)->sum('balance');
I don't know what scale you’re operating at, so chances are you may want to cache this somewhere. This would be what a “snapshot” would achieve if you were using an event sourcing approach. So you might take a snapshot each day or something.
1 like
Please or to participate in this conversation.