Choice PK and SK in DynamoDB

user_9397953 0
Viewed 4686

I have the following two items which I need to store in dynamodb

  • Artists
  • Songs

The artists have an ID (unique for artists), name and gender. The Songs have an ID (unique for songs), title, genre, artist and rating.

How should I model this in DynamoDB?.

I was thinking about this: ID as primary key and having a sort key which contains artist or song so they are distinguished. Is this a good choice? In examples I find I see more variety in the sort key.

What about the field artist in song items? Should I just point to the ID of the artist?

Update: I have many common access patterns. I can probably solve it by creating some indexes but still I have to choose for a good PK/SK:

get songs based on title
get songs based on rating
get songs based on genre
get songs based on artist

get artist based on rating
get artist based on gender
get artist based on name

Thanks

amazon-dynamodb modeling amazon-web-services database
2 Answers

One thing I found out (the hard way) about NoSQL (DynamoDB) modeling is that you need to know all your access patterns before you model your table. In and RDBMS, it's rather common to model first and optimize indexes later as access patterns change. This is not straightforward in NoSQL modeling (otherwise there would be massive migrations from RDBMS to NoSQL).

Having that said, I will now suggest a simplistic model and I will update my answer as the question is updated with access patterns (i.e. "I need to get all songs for an artist").

Artist:

PK: Artist-<Artist ID>, i.e. Artist-1234 SK: <Name> Attributes: Gender etc.

Song:

PK: Song-<Song ID>, i.e. Song-5678 SK: <Genre> Attributes: Genre, Artist ID, Rating

This approach will only allow you to get your entities by using their ID.

While it's common in NoSQL to de-normalize data (i.e. store artist data in song) for easiest/more efficient access, I'd go with storing the artist ID because it allows easier updates and better consistency.

user_15051460

DynamoDb is a very different world from the SQL world. There are trade-offs for getting unlimited scalability.

Do not rush to model your Db if the following appears absurd or new to you. Take your time to read more to understand.

  1. DynamoDb Tables have Partitions Keys which are used to automatically determine which physical partition data will be stored in. Do not mistake these with Primary Keys (also called PK). Partitions Keys are not generally unique.

  2. Single table is supposed to have store multiple entities (unlike SQL world). For example, user profile and user orders can be stored like this.

    PK: User#1 , SK: PROFILE# , username:dixitsandeep , addresses:[]

    PK : User#1 , SK: ORDER#109, items:["ITEM#1122","ITEM#9977]

    PK : User#1 ,SK: ORDER#2090, items:["ITEM#2288","ITEM#6655]

  3. When data is accessed using, Combination of Partition Key and Sort Key it results in a unique item.

  4. There is NO concept of JOIN ACROSS TABLES. Joins take place within multiple entities stored in a Single Table. You need to keep traditional normalization out of consideration.

  5. There are many patterns to simulate joins. One of them is to fetch data using Partition Key only (without specifying a Sort key) or with some filter on the Sort key. For example, you can use PK User#1 to fetch both PROFILE and ORDERS in a single query.

In other words, When data is accessed using, Combination of Partition Key and Sort Key it results in a unique item. When you broaden the Sort Key, you get multiple items. You simulate join by broadening the Sort Key filter.

  1. You almost always need to specify a partition key for your queries. Queries without partition keys could return partial data.

  2. You can create up to 20 indexes on a table. When you create an Index you generally make an attribute a PK, SK of the index. Unlike SQL world, in DynamoDb you specify the index when you want to fetch data using that index. You do not need to specify an index while inserting data. Indexes may help you to filter data based on attributes other than PK, SK of the main table.

With the above points in mind, the Partition Key choice should be such that it has lots of possible values. One way to think may be as follows: If you are having millions of users firing 1 million queries per second, then these queries should be landing to different Partition Keys. During high load cases, Partition Key may become a performance bottleneck if too many queries want to access data with the same partition key. That means the choice of Partition Key largely depends on how the application accesses the data: access patterns.

UserRole for example is a bad choice of PartitionKey because it is likely to group lots of data in a single partition.

AWS resources on DynamoDb Data modeling.

https://youtu.be/KYy8X8t4MB8

https://youtu.be/0uLF1tjI_BI

user_84617560
Related