I’m new to DB design and was wondering if the following design can be improved.
I want to create a bookkeping database which handles these use cases:
- For a given customer record all the transactions
- For a given customer give me the total balance
- For a given customer give me the total balance for a given time range
I was thinking of storing this in DynamoDB. The table schema would look like:
PrimaryKey: transactionId Attributes: (customerId (string), timestamp (epoch millis), transactionAmount (double))
The entries in these tables will never expire and we can expect around 1000 new transactions per second across many customers. As for getting the balances, we can expect around 10 reads per second.
I would then have two reverse indices:
- customerId – to support getting the entire balance for the customer
- customerId + timestamp (as sort key) – to support getting the balance within a given time range
Both of these indices would be used to get the balance which I will sum up on the server side.
I think this should scale pretty well and since I am use index on the table the query time should be pretty fast as well since I won’t be scanning the entire table.
Any feedback or callouts would be appreciated.