How to handle many to many relationship DynamoDB-CodePudding

Im new to dynamoDB and im trying to build an ecommerce store. I have a table with a user, product and order.

My access patterns are:

get all products in a users order

I can then use this for a similar issue with the users cart. But im not sure how. My user to order relationship is one to many and my product to order relationship is many to many.

My data looks like this:

   type Variant = {
    size: Sizes;
    quantity: number;
    price: number;
}

type OrderProduct = {
    id: string;
    orderId: string;
    product: Product;
    status: string;
    trackingId: string;
}

export type Product = {
    id: string;
    name: string;
    description: string;
    category: string;
    createdAt: string;
    variants: Variant[];
}

export type Order = {
    id: string;
    userId: string;
    products: OrderProduct[];
    createdAt: string;
}

export type User = {
    id: string;
    name: string;
    address: string;
}

Ive seen this on aws for many to many relationships: aws many to many relationships But this doesnt really explain how to do a one to many and then many to many query. Any advice and help with the query would be great!

CodePudding user response：

DynamoDB only allows you to query by partition key (and ranged key), or to query by indexes.

If you have different tables, you cannot do a join query. You might need to create a global secondary index and then do a query on that.

So, for instance, if your Product had a secondary index over a field called "order_id", you coud do:

const documentClient = new AWS.DynamoDB.DocumentClient();
const orderId = 1234; // the real order id

const options = {
  TableName: 'Product',
  IndexName: 'OrderIdIndex',
  KeyConditionExpression: 'order_id = :order_id',
  ExpressionAttributeValues: {
    ':order_id': orderId
  }
};

const response = await documentClient.query(options)

Keep in mind that this example is modifying your original structure. You might need to add that new index and attribute

CodePudding user response：

You do not do relationship queries in Dynamo. It is not a Relational Database, it is a document database.

This means most importantly, your normal way of storing data in multiple tables and usually by some whatever unique auto incrimented identifier in an SQL is a terrible way to do it in a dynamo

Instead, you need to store your data based on your access patterns - and this may feel very weird coming from SQL! You may even feel like you are duplicating data at times.

Since a Dynamo query requires you to know what the Partition Key is in order to query (you cannot do a search or a conditional on the PK) then the PK needs to be what you have to start your query.

so with your access pattern described, your PK must be the user. Then, a separate entry for each item in their cart would be the way to proceed - basically something like:

(EDIT: you can switch User for OrderID very easily too of course)

PK: User SK: ITEM#123456123

PK: User SK: ITEM#123491239

PK: User SK: Item#113322

and maybe even a

PK: User SK: META

with attribiutes like "total items" or "login time" or "sales offered" or whatever else needs to be tracked.

then if you query against the PK of USER, you get back a list of all their items. They remove an item, you remove the SK document associated with that item. They increase the amount, then you increase that items quantity attribute. ect.

This is in effect a One to Many relationship: One (the PK of User) and Many (SK's prefixed with ITEM#) - you can then do a query of PK=User, SK (beginsWith) ITEM# to retrieve all the items of a user.

But as you may be able to see, this can get very complex very fast if you are trying to do many different relationships - dynamo is not built for that. If you need to do anything deeper than a single relationship like this or need to be able to dynamically decide the relationships/queries at run time, then Dyanmo is not the solution, SQL is.