DB model for logging ongoing transactions

Question

I am used to have tables for ongoing activities from my former life as relational DB guy. I am wondering how I would store ongoing information like transactions, logs or whatever in a neo4j DB. Let#s assume I have an account, which is been assigned to a user A:

(u:User {name:"A"})

I want to keep track on transactions he does, e.g. deducting or adding a value:

(t:Transaction {value:"-20", date:timestamp()})

Would I do for every transaction a new node and assign it to the user:

(u) -[r:changeBalance]-> (t)

In the end I might have lots of nodes which are assigned to the user and keep one transaction each resulting in lots of nodes with only one information. I was pondering if a query that has a limit on the last 50 transactions (limit 50, sort by t.date) might still have to read all available transaction nodes to get the total sorting queue before the limit applies - this seems a bit unperformant.

How would you model a list of actions in a neo4j DB? Any hint is very appreciated.

Answer 1

If you used a simple query like the following, you would NOT be reading all Transaction nodes per User.

MATCH (u:User)-[r:ChangeBalance]->(t:Transaction)
RETURN u, t
ORDER BY t.date;

You'd only be reading the Transaction nodes that are directly related to each User (via a ChangeBalance relationship). So, the performance would not be as bad as you are afraid it might be.

Answer 2

Although everything is fine with your query - you are reading only transactions, that are related to this specific user - this approach can be improved.

Let's imagine that, for some reason, you application will work 5 years and you have user that have 10 transactions per day. It will result in ~18250 transaction connected to single node.

This is not great idea, from data model perspective. In this case if you want to filter result (get 50 latest transaction) on some non-indexed field, then this will result in full 18250 node traverse.

This can be solved by adding additional relations to database.

Currently you have such graph: (user)-[:HAS]->(transasction)

               (     user     )
              /       |        \
(transasction1) (transaction2) (transaction3)

You can add additional relation between transactions, to specify sequence of events.

Like that: (transaction)-[:NEXT]->(transasction)

               (     user     )
              /       |        \
(transasction1)-(transaction2)-(transaction3)

Note: there is no need to have additional PREVIOUS relation, because Neo4j store relationship pointers in both directions, so traversing backwards can be done at same speed as forward.

And maintain relations to first and last user transasctions:

(user)-[:LAST_TRANSACTION]->(transaction)
(user)-[:FIRST_TRANSACTION]->(transaction)

This allows you to get last transaction in 1 hop. And then latest 50 with additional 50 hops.

So, adding additional complexity, you can traverse/manipulate with your data in more efficient ways.

This idea come from EventStore database (and similar to them).

Moreover, with such data model User balance can be aggerated by wrapping up sequence of transaction. This can give you a nice and fast way to get user balance at any point.

---

Getting latest 50 transaction in this model can look like this:

MATCH (user:User {id: 1} WITH user
MATCH (user)-[:LAST_TRANSACTION]->(last_transaction:Transaction) WITH last_transaction
MATCH (last_transasction)<-[:NEXT*0..50]-(transasctions:Transaction)
RETURN transactions

Getting total user balance can be:

MATCH (user:User {id: 1}) WITH user
MATCH (user)-[:FIRST_TRANSACTION]->(first_transaction:Transaction) WITH first_transaction
MATCH (first_transaction)-[:NEXT*]->(transactions:Transaction)
RETURN first_transaction.value + sum(transasctions.value)