DynamoDB 1 big table or multiple small tables?

Question

I'm currently facing some questions regarding my database design. Currently i'm developing an api which lets users do the following:

• Create an Account ( 1 User owns 1 Account)
• Create a Profile ( 1 Account owns 1-n Profiles)
• Let a profile upload 2 types of items ( 1 Profile owns 0-n Items ; the items differ in type and purpose)

Calling the API methods triggers AWS Lambda to perform the requested operations in the DynamoDB tables.

My current plan looks like this:

It should be possible to query items by specifying a time frame and the Profile ID. But i think my design completely defeats the purpose of DynamoDB. AWS documentation says that a well designed product only requires one table.

• What would be a good way to realise this architecture in one table?
• Are there any drawbacks on using the current design?
• What would you specify as Primary/Partition/sort key/secondary indexes in both the current design and a one-table-approach?

Answer 1

I’m going to give this answer assuming that you need to be able to do the following queries.

• Given an Account, find all profiles
• Given a Profile, find all Items
• Given a Profile and a specific ItemType, find all Items
• Given an Item, find the owning Profile
• Given a Profile, find the owning account

One of the beauties of DynamoDB (and also a bane, perhaps) is that it is mostly schema-less. You need to have the mandatory Primary Key attributes for every item in the table, but all of the other attributes can be anything you like. In order to have a DynamoDB design with only one table, you usually need to get used to the idea of having mixed types of objects in the same table.

That being said, here’s a possible schema for your use case. My suggestion assumes that you are using something like UUIDs for your identifiers.

The partition key is a field that is simply called pkey (or whatever you want). We’ll also call the sort key skey (but again, it doesn’t really matter). Now, for an Account, the value of pkey is Account-{{uuid}} and the value of skey would be the same. For a Profile, the pkey value is also Account-{{uuid}}, but the skey value is Profile-{{uuid}}. Finally, for an Item, the pkey is Profile-{{uuid}} and the skey is Item-{{type}}-{{uuid}}. For all of the attributes of an item, don’t worry about it, just use whatever attributes you want to use.

Since the “parent” object is always the partition key, you can get any of the “child” objects simply by querying for the ID of the of the parent. For example, your key condition expression to get all the ‘ItemType2’s for a Profile would be

pkey = “Profile-{{uuid}}” AND begins_with(skey, “Item-Type2”)

In this schema, your GSI has the same keys as the table, but reversed. You can query the GSI for ‘Item-{{type}}-{{uuid}}’ to get the owning Profile, and similarly with a Profile is to get the owning account.

What I have illustrated here is the adjacency list pattern. DynamoDB also has an article describing how to use composite sort keys for hierarchical data, which would also be suitable for your data, and depending on your expected queries, it might be more suitable than using the adjacency list.

You don’t have to put everything in a single table. Yes, DynamoDB recommends it, but it is far more important to make sure that your application is correct and maintainable. If having multiple tables means it’s easier to write a defect free application, then use multiple tables.