I would like to create a table for orders in dynamodb ,each order is composed of (UserID, date, Unique ID, products and total) and I want to query the orders of specific user using userId sorted by date and i want to know how can I choose my partition key ? does it have to be unique and if so how can I make it unique ? in mongodb i would shard my orders based on userID how can i achieve the same using dynamodb ?
Let's say you have a user A with user ID - 1 and user B with user ID - 2 I am assuming A can order multiple times as well as B.
So you can design your table with a composite key ( combination of partition and sort key). In a table that has a partition key and a sort key, it's possible for multiple items to have the same partition key value. However, those items must have different sort key values. All items with the same partition key value are stored together, in sorted order by sort key value.
combination of partition key + sort key will result in a unique order. Here partition key will be userID and orderId will be unique. Hence you can query all orders for a particular user by userId using query operation. and for sorting with date you can use filter expressions in your query operation
if filter expressions does not helpy you with sorting with date then you need to use seperate index based on your createdAt field ( date field). Note :- indexes are costly.
DynamoDB allows for the specification of secondary indexes to aid in this sort of query. Secondary indexes can either be global, meaning that the index spans the whole table across hash keys, or local meaning that the index would exist within each hash key partition, thus requiring the hash key to also be specified when making the query.
Alternate design.
user userId as partition key and CreatedAt as sortKey, yes seconds difference will lead to a unique timestamp and then you can use key condition expression in your query. Querying DynamoDB by date