Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DynamoDb table design: Single table or multiple tables

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.
like image 755
Binh Luong Avatar asked Dec 28 '16 17:12

Binh Luong


People also ask

How many tables should I have in DynamoDB?

As a general rule, you should maintain as few tables as possible in a DynamoDB application. To better understand why that is (keeping few tables, ideally only one) and why it might be beneficial, let's briefly review the DynamoDB data model.

How many tables can you create in DynamoDB?

DynamoDB increased the default quota for the number of DynamoDB tables you can create and manage per AWS account and AWS Region from 256 to 2,500 tables. DynamoDB also increased the number of table management operations you can perform concurrently from 50 to 500.

What is difference between single table report and multi table report with example?

The main schema difference you will see between single and multi-table models is that single-table will have generically named attributes that are used to form the table's partition and sort key. This is required because different entity types will likely have differently named primary key fields.

When should you not use DynamoDB?

Even though the solution has many benefits, one of the major drawbacks is that the solution lacks an on-premise deployment model and is only available on the AWS cloud. This limitation does not allow users to use DynamoDB for applications that require an on-premise database.


2 Answers

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.

like image 123
Eugene Yarmash Avatar answered Oct 14 '22 18:10

Eugene Yarmash


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.

like image 13
letmesolve Avatar answered Oct 14 '22 16:10

letmesolve