Single table DynamoDB design tips

Question

I have an old application I am modernizing and bringing to AWS. I will be using DynamoDB for the database and am looking to go with a single table design. This is a multitenant application.

The applications will consist of Organisations, Outlets, Customers & Transactions. Everything stems from an organization, an organization can have multiple outlets, outlets can have multiple customers and customers can have multiple transactions.

Access patterns are expected to be as follows:

• Fetch a customer by its ID
• Search for a customer by name or email
• Get all customers for a given outlet
• Get all transactions for a customer
• Get all transactions for an outlet
• Get all transactions for an outlet during a given time period (timestamps will be stored with each transaction)
• Get all outlets for a given organisation
• Get an outlet by its ID

I've been reading into single table designs and utilizing the primary key and sort keys to enable this sort of access but right now I can't quite figure out the table/schema design. The customer will have the outletID and OrganiastionID attached so I should always know those ID's

Data Structure (can be modified)

Organisations:

• id
• Name
• Owner
• List of Outlets
• createdAt (timestamp)

Outlets:

• OrganisationId
• Outlet Name
• Number of customers
• Number of transactions
• createdAt (timestamp)

Customers:

• id
• OrganisationID
• OutletID
• firstName
• lastName
• email
• total transactions
• total spent
• createdAt (timestamp)

Transactions:

• id
• customerID
• OrganisationID
• OutletID
• createdAt (timestamp)
• type
• value

Answer 1

You're off to a great start by having a thorough understanding of your entities and access patterns! I've taken a stab at modeling for these access patterns, but keep in mind this is not the only way to model a solution. Data modeling in DynamoDB is iterative, so this is very likely that this specific design might not fit 100% of your use cases.

With that disclaimer out of the way, let's get into it!

I've modeled your access patterns using a single table named data with global secondary indexes (GSI) named GSI1 and GSI2. Each GSI has partition and sort keys named GSI#PK and GSI#SK respectively.

The base table models the following access patterns:

• Fetch customer by ID: getItem where PK=CUST#<id> and SK = A
• Fetch all transactions for a customer: query where PK=CUST#<id> and SK begins_with TX
• Fetch an outlet by ID: getItem where PK=ORG#<id> and SK = A
• Fetch all customers for an outlet: query where PK=OUT#<id>#CUST

That last access pattern may require a bit more explanation. I've chosen to model the relationship between outlets and customers using a unique PK/SK pattern where PK is OUT#<id>#CUST and SK isCUST#<id>. When your application records a transaction for a particular customer, it can insert two records in DDB using a batch write operation. The batch write operation would perform two operations:

• Write a new Transaction into the Customer partition (e.g. PK = CUST#1 and SK = TX#<id>)
• Write a new record to the CUSTOMERLIST partition (e.g. PK = OUT#<id>#CUST and SK = CUST#<id>). It this record already exists, DynamoDB will just overwrite the existing record, which is fine for your use case.

Moving onto GSI1:

GSI1 supports the following operations:

• Fetch outlets by organization: query GSI1 where GSI1PK = ORG#<id>
• Fetch transactions by outlet: query GSI1 where GSI1PK = OUT#<id>
• Fetch transactions by outlet for a given time period: `query GSI1 where GSI1PK=OUT# and GSI1SK between and

And finally, there's GSI2

GSI2 supports the following transactions:

• Fetch transactions by organization: query GSI2 where GSI2PK = ORG#<id>
• Fetch transactions by organization for a given time period: query GSI2 where GSI2PK=OUT#<id> and GSI2SK between <period1> and <period2>

For your final access pattern, you've asked to support searching for customers by email or name. DynamoDB is really good at finding items by their primary key. DynamoDB is not good for search, where fuzzy or partial matches are expected. If you need an exact match on email or name, you could do that in DynamoDB by incorporating email//name in the primary key of the User item.

I hope this gives you some ideas on how to model your access patterns!