DynamoDB Single Table Design Best Practices From Perspective of SQL

Question

I needed help designing my DynamoDB table as this is my first time using it.

I am thinking from the perspective of SQL and trying to design in NoSQL(DynamoDB) with a Single Table approach.

I have a few Tables which were Users, PaymentHistories, Campaigns, and Stores.

• 1 Store can host many Campaigns.
• 1 User can participate in many Campaigns.
• 1 User can have many PaymentHistories.
• 1 Store can have many PaymentHistories.
• The user will have histories record of participating in campaign activities.

Based on the above criteria, I have designed a sample as below.

{
 "storeId": "7c9eafab-ad34-4247-8908-0ab6552ddfa6",
 "balance": 0,
 "cashback": 0,
 "challenges": [
  {
   "challengeEndDatetime": "1677974400",
   "challengeId": "3e1e10ac-39c8-49ad-8c84-80a576104710",
   "challengeStartDatetime": "1677628800",
   "totalPlayer": 25,
   "users": [
    {
     "history": {
      "1": {
       "activity": "Completed Challenge One",
       "pointsReflection": 50
      }
     },
     "points": 50,
     "userId": "ae847af6-0a74-4fa4-afff-58cd8f7e3ee3"
    }
   ]
  }
 ],
 "storeName": "My Store",
 "storeType": "restaurant"
}

I m not sure if is it suitable to add nested payment history attribute to both the store and the user. From the JSON provided, the relationship is Stores --> Challenges --> Users --> History. I will have a separate User table here to handle sensitive user information.

Answer 1

The main difference in the design approaches for SQL and NoSQL respectively is that in the former case, you are looking through the lens of entities and relationships (mostly with the goal of minimizing data duplication); in the latter, your primary concern is query efficiency, in other words, answering the question: "How do I store the data in such a way that all the data needs of my users are met with querying a single record in the database, for a highest possible number of user queries?"

(Often, but not always, this latter design concern is best answered by relaxing the requirement for the minimal data duplication within the data store, as well as allowing for eventual, rather that strict, data consistency.)

Note that your question speaks only to entities and relationships, not user query patterns. So all of the information you give is pertinent to an SQL design, and almost none of it, for a NoSQL one.