How to create multiple DynamoDB entries under the same primary key?

Question

I am developing a skill for Amazon Alexa and I'm using DynamoDB for storing information about the users favorite objects. I would like 3 columns in the database:

1. Alexa userId
2. Object
3. Color

I currently have the Alexa userId as the primary key. The problem that I am running into is that if I try to add an entry into the db with the same userId, it overwrites the entry already in there. How can I allow a user to have multiple objects associated with them in the db by having multiple rows? I want to be able to query the db by the userId and receive all the objects that they have specified.

If I create a unique id for every entry, and there are multiple users, I can't possibly know the id to query by to get the active users' objects.

Endorsed

Answer 1

You have two main options here, and they have different pros and cons

First you can use a composite primary key. It allows you to have 1-to-N relationships in your table.

In your case it can be something like:

AlexaId - partition key

Object - sort key

It allows you to get all objects for a given AlexaId.

Limitations of this method:

• You can store up to 10GB of data with the same partition key, it means that sum of sizes of all items for the the same AlexaId is limited by 10GB.

• Working with multiple items in this way is not atomic. DynamoDB does not natively supports transactions, so you can't have atomicity when you work with items. You can use DynamoDB transactions library for Java, but transactions are quite expensive in DynamoDB.

Another option is to store these objects in an item itself. While in a regular SQL database you can only store scalar values in a row in DynamoDB every item can contain complex types, such as sets, lists, and nested objects.

You can read more about DynamoDB data types here.

With this you can have a table like this:

AlexaId - partition key

Objects - a list or set or a map

Pros of this method:

• DynamoDB natively supports atomicity per item. You can perform atomic updates of an item and even have optimistic locking

Limitations of this method:

• An item size is limited by 400KB, so you can't store a lot of data in it.

Labanna

Answer 2

If the combination of Alexa ID + Object is unique (non-duplicated), then you should configure your table with a composite primary key:

• Partition Key = Alexa ID
• Sort Key = Object

You can then efficiently retrieve the row for a given Alexa ID + Object, which would be fully indexed.

See: Working with Queries

Christiansen

The poster asks precisely about being able to query "get the active users' objects". – Farcy

Answer 3

DynamoDB is a NoSQL-like, document database, or key-value store; that means, you may need to think about your tables differently from RDBMS. From what I understand from your question, for each user, you want to store information about their preferences on a list of objects; therefore, keep your primary key simple, that is, the user ID. Then, have a singe "column" where you store all the preferences. That can either be a list of of tuples (object,color) OR a dictionary of unique {object:color}.

When you explore the items in the web UI, it will show these complex data structures as json-like documents which you can expand as you will.

Farcy

Answer 4

you cannot create multiple entries with same primary key. Please create composite keys (multiple keys together as primary key). Please note you cannot have multiple records of same combination

Doretheadoretta

Answer 5

You can use a sort key, so for example you can have AlexaUserID as your primary key, and than Object as your sort key.

Than if you want to query all the users that have the same AlexaUserID you can use the Query API for DynamoDB. (This is important because get_item() wont work, because for that you have to provide both primary key and also the sort key).

For example;

import boto3
from boto3.dynamodb.conditions import Key

client = boto3.resource('dynamodb')
table = client.Table('yourCoolTableName')

let userName = "thisIsMyAlexaUserName"

response = table.query(
     KeyConditionExpression=Key('AlexaUserID').eq(userName)
)    

items = response['Items']

You can read more about the Query API here, also here is the example from AWS's own documentation.

Denise