DynamoDb table design: Single table or multiple tables

Question

I’m quite new to NoSQL and DynamoDB and I used to RDBMS. I’m designing database for a game and we're using DynamoDB and AWS Lambda for our backend. I created a table name “Users” for player profile that contains the user information and resources. Because the game has inventory system I also created a table name “UserItems”.

It’s all good until I realized DynamoDB don’t have transaction and any operation that is executed on both table (for example using an item that increase resource) has a chance of failure on one table while success on other and will cause missing data which affect our customers.

So I was thinking maybe my multiple tables design is not good since it’s a habit of me to design multiple table when I’m working with RDBMS. Which let me to think of storing the entire “UserItems” as hash in “Users” but I’m not sure this is a good practice because the size of a single row in Users table will be really big (we may have 500 unique items per users) and each time I pull or put data from/to “Users” (most of the time don’t need “UserItems” data) the read/write throughput will be also really large.

What should I do, keep the multiple tables design and handle transaction manually or switch to single table design? Or maybe there is a 3rd option?

Updated: more information about my use case

Currently I have 2 tables

• Users: UserId (key), Username, Gold
• UserItems: UserId (partition key), ItemId (sort key), Name, GoldValue

Scenarios:

1. User buy an item: Users.Gold will be deduced, new UserItem will be add to UserItems table.
2. User sell an item: Users.Gold will be increased, the Item will be deleted from UserItems table.

In both scenarios above I will have to do 2 update operation for 2 tables which without transaction there is a chance one of them failed.

To solve that I consider using single table solution which is a single Users table with 4 columns UserId(key), Username, Gold, UserItems. However there are two things I'm worried about:

1. Data in UserItems might be come to big for a single cell because one user could have up to 500 items.
2. To add/delete item I have to pull the UserItems from dynamodb, add/delete item and then put it back into Users. So I have to do 1 read and 1 write operation for 1 action. And because of issue (1) the read/write data size could become really big.

Answer 1

NoSql database is best suited for non-trasactional data. If you bring normalization(splitting your data into multiple tables) into noSQL, then you are beating the whole purpose of it. If performance is what matters most, then you should consider only having a single table for your use case. DynamoDB supports Range Keys, and also supports Secondary Indices. For your usecase, it would be better to redesign your table to use Range Keys. If you can share more details about your current table, maybe i can help you with more inputs.

Answer 2

FWIW, the AWS documentation on NoSQL Design for DynamoDB suggests to use a single table:

As a general rule, you should maintain as few tables as possible in a DynamoDB application. As emphasized earlier, most well designed applications require only one table, unless there is a specific reason for using multiple tables.

Exceptions are cases where high-volume time series data are involved, or datasets that have very different access patterns—but these are exceptions. A single table with inverted indexes can usually enable simple queries to create and retrieve the complex hierarchical data structures required by your application.