How to count the number of times the userid is present in Dynamodb

Question

I have created a amazon lex chatbot that raises the ticket for the organisation. It asks for 5 input fields (empid, name, description, impact, urgency, emailid) and stores in aws lambda. Till now, I have been storing the input fields that I get from chatbot into dynamodb through aws lambda in python. Now,I want to check how many times a particular user has raised a ticket. I am a beginner to database concepts.

Sample conversation.

User:Hi   
Chatbot:Enter your id?   
User:q123    
Chatbot:enter your name?   
User:xxx    
Chatbot:enter your desc?       
User:sdbsb       
Chatbot:enter your emailid?         
User:xxx@yyy.com     
Chatbit:your ticket is raised.       
User:I want to check till now the number of tickets I have raised     
Chatbot:you have raised 4 tickets till now.

Sample code:

import json
import logging
import re
import http.client
import mimetypes
import boto3

logger = logging.getLogger()
logger.setLevel(logging.DEBUG)


def close():
    val=  {
             "dialogAction":
            {
                "fulfillmentState":"Fulfilled",
                "type":"Close",
                "message":
                 {
                     "contentType":"PlainText",
                     "content":"Hey your ticket has been raiised"
                 },
                'responseCard': {
                'version': '1',
                'contentType': 'application/vnd.amazonaws.card.generic',
                
                    }
                }
    print(val)
    return val


def lambda_handler(event, context):
  
    dynamodb=boto3.resource('dynamodb')
    table=dynamodb.Table('employee')
    slots = event['currentIntent']['slots']

    empidemployee= event['currentIntent']["slots"]["empidemployee"]
    latestdesc= event['currentIntent']["slots"]["latestdesc"]
    latestservice= event['currentIntent']["slots"]["latestservice"]
    latestimpactvalue= event['currentIntent']["slots"]["latestimpactvalue"]
    latesturgency= event['currentIntent']["slots"]["latesturgency"]
    basicinfo=event['currentIntent']["slots"]["basicinfo"]
    
    jsonStrings ={
            'empidemployee':empidemployee,
            'desc':latestdesc,
            'service':latestservice,
            'impact':latestimpactvalue,
            'urgency':latesturgency,
            'emailid':basicinfo
            
        }
    print(jsonStrings)

    table.put_item(
        Item=jsonStrings
        )

    
    val=close()
    return val
    return {
        'statusCode':200,
        'body':'added'
    }

Answer 1

You haven't given us any information about your table structure, so it'll be challenging to provide specific advice.

When working with DynamoDB, it's critical that you understand the access patterns of your application. For example, you appear to have the following access patterns:

• Fetch tickets by user
• Fetch number of tickets by user

You may also have the following access patterns:

• Fetch OPEN tickets by user
• Fetch CLOSED tickets by user

And you might have:

• Fetch all OPEN tickets across all users
• Fetch all CLOSED tickets across all users

Each of these access can be easily be supported by DynamoDB, but you'll need to design how you store your data to support each access pattern. In a relational database, it would be enough to store User and Ticket information because you can use SQL to perform ad-hoc queries:

select count(*) from tickets where user_id = "q123"

but that's not how DynamoDB works.

Can you elaborate on your access patterns and your current table design?

UPDATE:

You describe a one-to-many relationship between employees and tickets. Your primary access patterns include getting tickets per user and getting a count of the number of tickets per user. To support those access patterns, I'd design the table as follows (I've left out some fields for brevity):

This table shows two Employees. Employee 1234 has 3 Tickets. Employee 5678 has 1 Ticket.

I've color coded a few concepts I want to bring to your attention. The blue box is the Employee partition. The employee partition contains a collection of Ticket items (highlighted in green) and an employee profile (highlighted in red).

In the employee profile, I've included a num_tickets attribute. When inserting/deleting a ticket, you can add/subtract that number. As a result, you'll always be able to fetch the current count by grabbing the Employee's profile.

A few example queries (in pseudocode):

• Fetch Employee Tickets: PK = EMPLOYEE#<id> AND BEGINS_WITH(SK, 'TICKET')
• Fetch Employee Profile: PK = EMPLOYEE#<id> AND SK = PROFILE

If you are new to DynamoDB, much of this may sound confusing. DynamoDB requires a very different mindset than working with relational databases. How you structure your data is critical to supporting your access patterns.

I would highly recommend viewing Alex DeBries talk on DynamoDB data modeling. He has a gift for explaining complex DynamoDB data modeling topics in a very accessible way.