Group transactions sums by months with projector

[bee-interactive]

Hi everyone!

I'm learning the event sourcing pattern, so I'm quite new to this.

For my question, I'm using the spatie/larabank-aggregates repo.

I want to know the sum for each accounts, but grouped by month. The example projector gives me the "count" of each transactions.

How can I structure my database to achieve this? Since I know that I can have multiple accounts with each of them, sums by months..

I've tried to create a new projector called TransactionSumProjector and a new table called transaction_sums. The structure imagined looks like this:

id
uuid
user_id
year_month (data would look like this: 2022-01)
amount
timestamps fields

So, basically, it would create a new entry based on the sums of the transactions grouped by months.

Is this the right way to achieve this? Could be another better solution?

Any help would be appreciated!
Thanks!

[brendt]

I'd call the projection something like transactions_per_month_and_account or something alike.

Another valid approach is to project a more "normalized" table, maybe transactions per day and account, or even just transactions with their exact date and account, and aggregate the data when reading it.

Such a normalized approach offers more flexibility, but a dedicated projection can be helpful, for example, when performance becomes an issue or when dealing with complex aggregations.