I am doing my best to understand DynamoDB data modeling but I am struggling. I am looking for some help to build off what I have now. I feel like I have fairly simple data but it's not coming to me... More on stackoverflow.com

Hello and welcome to the world of DynamoDB and NoSQL databases! I'm gonna walk you through a schema that I've given at least some thought to that might work for such a scenario. Before I begin, I should preface this by saying that I am by no means a NoSQL design expert--I hardly even consider myself a novice. What that means is that the schema I come up with almost certainly isn't the best schema to use (and is probably on the lower end of the quality scale); regardless, it makes sense to me so it's what I'll use. NoSQL databases are really good at certain queries but otherwise terrible at others. Thus, you absolutely need to know your access patterns before designing your schema. After looking up what a balance sheet is, I determined these probable access patterns: Current company assets Current company liabilities Current company owner equity Notice how I say "current" in all of these access patterns; recording historical data would effectively require snapshotting the data (at least based on the schema I came up with) in some way; how you achieve this would be up to you. Regardless, assuming this is all for one company (i.e., the company is making an internal tool for tracking their financials), we'll be using a Composite Primary Key. A Primary Key is basically the one and only actually unique value of your DynamoDB table, and it serves as the identifier for any given record within your DynamoDB table. A Composite Primary Key basically means that instead of containing only one attribute that must be unique (the Partition Key), the Primary Key is made up of two attributes that together must be unique (a Partition Key and Sort Key). Your Primary Key is going to consist of a Partition Key that is the "type" of the transaction (whether it is an asset, liability, or owner/shareholder equity) and a Sort Key that consists of the following: Transaction category (i.e., current assets, other assets, etc.) Transaction subcategory (i.e., cash within current assets, etc.) Transaction date and time (for this you can go as granular as you wish, but I would suggest at least hourly because otherwise you severely limit yourself in when transactions can happen, which you can't control; for my purposes, I will go down to the minute.) As an example, if I have a current cash asset that I added on November 5th, 2020 at 21:05 (9:05 PM), it's Primary Key would look like this: Primary Key: Asset Sort Key: Current Asset#Cash#2020#11#05#21#05 In my Sort Key, notice how I've divided each part of the Sort Key using hashtags/pound symbols ("#"). When used in a Sort Key, these are akin to the "/" (or "\") symbol used to designate each level within a file directory (i.e., C:\Users\tycoon\Desktop\hello world.txt). Technically speaking, I don't need to use the hashtags to separate each part of the Sort Key, but doing so makes it much easier to identify each part of the Sort Key (and allows you to use the Sort Key as part of your actual data storage, simply by splitting the string on each hashtag). Because I've used a Primary Key that's structured like this, it allows me to "natively" do the following queries: All transactions of a certain type (i.e., all assets or all liabilities) All transactions of a certain category within a certain type (i.e., all "current assets" within our assets) All transactions of a certain subcategory that are within a certain category and certain type (i.e., all cash transactions that deal with our current assets and are a part of our assets.) All transactions of a certain date and/or time (though the time must include the date; you cannot query for all transactions that occurred at 11:00 on any day of the month) that are within a certain subcategory, category, and type (i.e., all transactions that have occurred between 21:00 and 21:59 on November 5th, 2020, that have to do with cash within our current assets). Since this is storing transactions, I'll assume you at the very least want to store the following attributes: Payer (the entity sending the asset) Payee (the entity receiving the asset; aka the recipient) Asset Value Asset Type Assets Transferred (this can be an internal ID, a name; basically, a thing that help you identify what exactly was transferred) With the previous in mind, you can create two Global Secondary Indexes for your DynamoDB table. Global Secondary Indexes (aka "GSI"s) allow you to query any value stored as the GSI's Primary Key similarly to the actual DynamoDB table; this is at the cost of increased storage usage and increased read and write capacity usage. GSIs function similarly to root DynamoDB tables, but aren't entirely the same as creating a second DynamoDB table. Regardless, you can create a few GSIs, depending on what you'll likely need to access: One GSI with the Payer as its Primary Key and Payee as its Sort Key One GSI with the Payee as its Primary Key and Payer as its Sort Key One GSI with the Asset Type as its Primary Key (optionally, GSIs with Asset Type as its Partition Key and then Payer and Payee (individually) as their Sort Keys would also be an option, if you predict you'd need to get all assets where you were the payer, etc.) Assuming you create at least the first two GSIs I told you about, you can now also do the following queries: Transactions where you were the Payer Transactions where you were the Payer and "Company A" was the Payee Transactions where you were the Payee Transactions where you were the Payee and "Company A" was the Payer You might notice in our GSIs that records are probably not going to be unique, and that's OK. Unlike the root DynamoDB table, records in GSIs do not need to have unique Primary Keys. If you query for a Primary key that would result in multiple records under that GSI, then the GSI will simply return all the records it has that fit the Primary Key you provided. Due to our GSI schema, this does have the side effect that you can't query for specific transactions between two entities. If you wanted to add the balance before and after any given transaction, you'll want to implement data aggregation. I won't get into the specifics of how to accomplish this, but one way of doing this is basically you start at $0 of assets and every time a transaction is completed, you take your total asset count and, if the transaction is an asset, add the value of the asset to the total, otherwise if the transaction is a liability, subtract the value of the asset from the total, and then store these values in the table under, for example, the "Total" Partition Key and "Total" Sort Key (remember, this is a NoSQL database, so no need to store the same things in each record). Alrighty, well, assuming I didn't miss anything, that's how I approached the problem you proposed. As much as this is a learning experience for you, this was (despite the fact that I typed this out for about an hour) quite fun for me to do as I love DynamoDB and have rather recently figured out better ways of data modeling and schema design within DynamoDB (though I still don't even consider myself a novice really). This was a great challenge for me to do that I hope you learn something from. NoSQL is really very different from a traditional relational database and associated RDBMS, and it definitely takes some learning to realize the power of NoSQL databases, as you basically have to "unlearn" everything you learned whilst figuring out traditional relational databases. However, NoSQL databases can be great for many projects and many use cases; it's just a matter of choosing the right one for the job. If you're curious about me, I have started with NoSQL databases, specifically DynamoDB since I went straight into AWS (as much as I probably shouldn't have...especially now that I say that "I love AWS a little too much"); thus, most of my experience is with DynamoDB and I have comparatively little experience with traditional SQL databases. Regardless, if you have any questions for me, feel free to ask them in a reply to this comment! I'll do my best to answer them as best I can. Happy coding, and happy learning! P.S. If you're wondering how many of these absolutely ridiculously long posts I've made (all in my attempt to help people learn AWS), then that number is more than I'd like to admit. You're free to look at my post history to see just how many times I've posted giant comments like this. :3 More on reddit.com

DynamoDb requires that you set up the key-schema and the attribute-definitions for those fields. Here you can see the required fields and how to provide them: https://docs.aws.amazon.com/cli/latest/reference/dynamodb/create-table.html#synopsis One thing I should caution, if you're just getting started, definitely check out the talks by Rick Houlihan https://www.youtube.com/watch?v=6yqfmXiZTlM More on reddit.com

One of the things that I hear about single table design is that you need to understand all your access patterns up front. But rarely do requirements come up front. Agile (putting aside the cargo cult) is often executed in a way that reacts to changing demands and this seems to be an admission of flawed requirements, or information that only comes later. With the extra cognitive load of planning a single table design I feel like it’s going to optimise RCU at the cost of refactoring exercises down the track. But I’d be happy to understand opposing points of view. More on reddit.com