Question 95850 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

How to handle many to many relationship DynamoDB – Amazon web services

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!

Tags:

2

Answers


  1. 0

    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

    Edit

    Keep in mind that there might be some delay for the index propagation. For example, if you insert a new Product, and you immediately want to search using the Index by order_id, DynamoDB might tell you that there is no product (because its propagating the data). If that small delay is not acceptable, you might prefer to first query the Order, and then query each product by Id (you could use batchGet if needed)

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search