英文:
How to design many-many relationships in dynamodb so that I can fetch all the items where an entity is present at any end of the relationship?
问题
只有1对1的通话是可能的。所以我想要将通话的详细信息存储在我的DynamoDB表中。有一些访问模式，但我唯一感到困惑的主要访问模式是：
列出所有用户的所有通话，包括呼入和呼出通话，按时间戳排序。
我提出的解决方案有：
- 为每通电话创建两个条目 - 一个以user1为主键(pk)，另一个以user2为主键(pk)。但这意味着当我想要更新通话状态或时间戳时，我必须更新这两个条目。
- 主键(pk)为user1，GSI1-PK为user2 - 但使用这种方法，我需要获取一定数量的按时间戳排序的已接收通话，然后获取最后一项的时间戳，然后获取该时间戳之后的所有通话。接下来，我将需要将这两个列表连接起来，并在应用程序逻辑中按时间戳排序。
是否有更好的方式来满足这个访问模式？如果没有，你会建议以上哪种方法？
英文:
I have a call application where one user can call another user. Only 1v1 call is possible. So I want to store details of calls in my dynamodb table. There are a handful of access patterns but the main and only access pattern I'm struggling to find a good solution for is:
List all, that is, both incoming and outgoing calls of a user sorted by timestamp.
The solutions I came up with are:
- Create 2 entries for each call - 1 with user1 as pk and 1 with user2 as pk. But this will mean I will have to update both entries when I want to update call status or timestamps.
- PK as user1 and GSI1-PK as user2 - But with this approach, I will have to fetch certain number of calls received, sorted by timestamp, then get the timestamp of the last item, and fetch all calls made after that timestamp. Then I'll have to concatenate both those lists and sort it by timestamp in application logic.
Is there a better way to fulfill this access pattern? If not, which one of the above methods would you suggest?
答案1
得分: 1
这是我会做的：
让基本表表示呼出通话。因此，主键是呼叫者，SK是时间戳，接收者是有效负载中的属性。
创建一个GSI表示呼入通话。因此，GSI的主键是接收者，GSI的SK是时间戳，呼叫者是有效负载中的属性。
如果你想要查询某人的所有呼入或呼出通话，你需要进行两次查询调用，然后在呼叫者代码中将结果合并在一起。你可以通过使用SK值轻松限制时间段。
这种设计还允许您查询某人是呼叫者或接收者的时间。
使用GSI很方便，因为您只需要对每次呼叫执行一次实际更新，然后让GSI自动填充。
你也可以选择第一种选项。这会使编写代码有点复杂，但查询会简化一些。通常这种类型的应用程序写入较多，所以我倾向于简化编写过程，但两种方法都可以工作。
英文:
Here's what I would do:
Have the base table represent calls out. So the PK is the caller, SK is the timestamp, and the recipient is an attribute in the payload.
Have a GSI represent calls in. So the GSI PK is the recipient, the GSI SK is the timestamp, and the caller is an attribute in the payload.
If you want all calls in or out by someone, you do two Query calls and in the caller code merge the results together. You can limit by time period easily by using the SK values.
This design also lets you query for times a person was the caller or recipient only.
Using a GSI is convenient because you only have to do one actual update per call and can let the GSI auto-populate.
You could do your first option too. It makes writing a little trickier but makes querying a little simpler. Usually these kind of apps are write-heavy, so I'd err on the side of simplifying the writing process, but either way would work.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论