CODEWITHSUNDEEP
nosqlamazon-dynamodb
Dylan w
Dylan w

Reputation: 2896

DynamoDB - Associative table - Many to Many relationship

I have a scenario in DynamoDb, where records have a many-to-many relationship.

In SQL, normally I would create an associative table to seperate the records into one-to-many relationships.

For example:

  1. Stories can have multiple locations
  2. Locations have multiple stories

Here is a sample record:

{
  "storyId": "asd239ruefjsp32wf",
  "name": "Donut store",
  "locations": [
    {
      "locationId": "asdas23r23",
      "name": "New South Whales",
      "abbreviation": "NSW"
    },
    {
      "locationId": "sdgkhsdf98",
      "name": "Queensland",
      "abbreviation": "QLD"
    }
  ]
}

This could possibly be separated into 3 tables:

Stories

Locations

StoriesLocations (with a GSI - partitionKey = locationId)

My big issue is, a user can search for stories using more than 1 locationId.

GET /stories?locations=sdgkhsdf98,asdas23r23

Querying the StoriesLocations GSI using each storyId separately doesn't seem like a good solution, especially if I then have to get all the story data afterwords and manage pagination.

There is only 1 country currently, that has 7 locations. So only a handful of locations will ever be searched.

Is there a more efficient way of storing the data? or even querying it?

I have chosen DynamoDB because of it's speed to get going and normally I do frontend development. So setting up a SQL database I have not had much experience with. I will also be using Appsync's real time chat stater, which by default uses DynamoDB.

Upvotes: 1

Views: 277

Answers (1)

Raymond
Raymond

Reputation: 24

I think only 1 table and 1 Local secondary index(LSI) ie. Stories and locations as stories' table's Local secondary index(LSI) are needed in your scenario. The Stories table uses storyId as its Hash/partition Key and locations as its Sort/range Key. As for the LSI, you can use locations as the sort key and project any Stories' table's attributes you need in the LSI as you can query against it later. this more info on DyamoDB LSI and sort key

Look here for more details.

Stories
HK storyId
SK locations
name
...


(LSI) storiesLocationsIndex
SK locations
name
...

Hope this helps

Upvotes: 1

Related Questions