Data modelling in dynamo db for Ticket Management System

user_2374423 0
Viewed 88

I am working in Dynamo DB for the first time . My assignment is Ticket Management System where it has 3 entities Department , User and Ticket. The relationship between each entity is.

enter image description here

I have identified the following access patterns

  1. Fetch a Department.
  2. Fetch all users in Department
  3. Fetch a given user in Department
  4. Fetch all Tickets belongs to the Department
  5. Fetch all Tickets assigned to the User

for which i defined the following data model . I am thinking of creating GSI with Tickets as PK and User as SK to do 4 & 5

enter image description here

On a higher level I need to perform 2 updates . I can update the User to which the ticket is assigned and I can update the ticket status as inprogress, resolved . And in the table I have Ticket details as JSON object as below.

enter image description here

I need help from from the experienced people whether my understanding and approach is efficient.

amazon-dynamodb amazon-web-services data-modeling
1 Answers

I think you're on the right track. I'd design it as a table with two Global Secondary indexes. The base table looks like this:

Base Table

The first Global Secondary Index like this (GSI1):

Global Secondary Index 1

The second Global Secondary Index like this (GSI2):

Global Secondary Index 2

Now for the why:

This design allows you to easily update the following things:

  • A user's department
  • A ticket's status if you know the ticket Id
  • A ticket's user if you know the ticket Id
  • A ticket's department if you know the ticket Id

You can get a bunch of information from this model:

Fetch a Department.

Query the base table with the department name or list all departments

Fetch all users in Department

Query GSI 1 with the Department Name and filter the sort Key using begins_with = USER#

Fetch a given user in Department

Sound like you know the UserId, so do a GetItem on the base table. If that's not the case, do the query mentioned in "Fetch all users in Department".

Fetch all Tickets belongs to the Department

Query GSI 1 with the department name as the PK and filter the SK using begins_with = Ticket#

Fetch all Tickets assigned to the User

Query GSI 2 with the user id as the PK and filter the SK using begins_with = Ticket#

user_64858810
Related