0

related question: https://stackoverflow.com/questions/65165530/aws-dynamodb-table-design

I am trying to design a schema that will support the use cases (with 1 query per use case):

  • "get all items(including all attributes) with assigned_user = user_id"
  • "get all items(including all attributes) with owned_user = user_id"
  • "get all items(including all attributes) by item_ids"
  • "get all users(including all attributes) by user_ids"

note that:

  • an item can be assigned for multiple users.
  • I do not want to use a filter, I am trying to create a "Global secondary index" that will solve it.
  • another (less preferable) possibility is to find a query that will get all the items ids from the user and then query the ids - but I want to do it in 1 query
  • relationship between Users and Items is many-to-many

table:

  • user: p_key: "user_id" , s_key: "type", attributes: {owned_items: set<user_id> , assigned_items: set<user_id>, ...}
  • item: p_key: "item_id" , s_key: "type", attributes: {metadata: string, item_assignees: set<user_id>, item_owners: set<user_id> , ...}

any suggestions?

3
  • Are you absolutely forced to use a key-value store for something that would be trivial for a common RDBMS to handle?
    – bbaird
    Dec 8 '20 at 15:47
  • how would you do it in RDBMS ? @bbaird
    – Yoni Maman
    Dec 9 '20 at 6:22
  • A User table, an Item table, and a UserItem table (or something similar - I'm not sure what the assigned user/owner represents in your context. If you want to update your question with more detail I'd be happy to offer guidance.
    – bbaird
    Dec 9 '20 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.