我最近刚刚开始学习DynamoDB单桌设计。现在，我正在尝试建模具有以下属性的货运更新数据：
- 一个帐户有多个用户
- 一个帐户拥有多个货物的
- 运输可以每次货运更新时多次更改ETA
- ，将插入新记录，插入
访问模式：
- 获取所有货物的所有货物都以ETA的上升订单订购的最后更新状态，以
- 获得给定装运，获取按时间顺序更新
上面提到的2个访问模式。如果本身本身，我每次发货只有1个记录，那么我只能更新货物更新项目的排序键，为 shpm＃55abc ，以及ETA给定帐户的所有货物检索直截了当，这是通过GSI Accounteta 。
如何解决此问题以获取所需的访问模式？我应该考虑有一个单独的桌子来进行货运更新审核，即仅存储货运更新？因此，当我需要访问模式＃2时，我通过货物ID查询此审核表以获取所有按时间顺序更新。但是，我觉得这打败了单个桌子设计的目的。
I recently just started learning about DynamoDB single table design. Now, I am trying to model Shipment Update data that has the following properties:
- an account has multiple users
- an account has multiple shipments
- a shipment can change eta multiple times
- each time there's a shipment update, a new record will be inserted
Access patterns:
- get all shipments of an account displaying the last updated status ordered by eta in an ascending order
- for a given shipment, get the chronological updates
I am having a difficulty trying to resolve the 2 access patterns mentioned above. If, per se, I only have 1 record per shipment, then I can just update the sort key for the shipment update items to be shpm#55abc and the retrieval of all shipments for a given account by eta is straight forward, which is via the gsi accountEta.
How do I resolve this to get the access patterns I need? Should I consider having a separate table for the shipment update audit, i.e. to store just the shipment updates? So that when I need access pattern #2, then I query this audit table by the shipment id to get all the chronological updates. But, I feel like this defeats the purpose of the single table design.
发布评论
评论（1）
单桌子设计非常适合这些访问模式。使用超负荷，*：
装运具有“当前”记录。添加一个全局辅助索引（
gsi1），以创建一个以ETA顺序查询的替代主键（模式＃1）。对货物的所有更改均以此“当前”记录的更新执行。接下来，启用 old_image 写入表格为更改控制记录。这可以通过运输和帐户启用模式2。
这种方法的一种优点是，单个查询操作可以以相反的顺序检索当前的发货记录及其完整/部分变更历史记录。这就是当前记录键上
x _前缀的原因。具有pk = shpmt＃55abc和sk＆gt; =“ update”的键表达式的查询（X_Current）和最新的更新记录。*这对您来说是否是一个很好的解决方案，取决于预期的读/写入量。
A single-table design is a good fit for these access patterns. Use overloadable, generic key names like PK and SK. Here is one approach*:
Shipments have a "current" record. Add a global secondary index (
GSI1) to create an alternate Primary Key for querying by account in ETA order (pattern #1). All changes to the shipment are executed as updates to this "current" record.Next, enable DynamoDB Streams on the table to capture shipment changes. Each time a "current" record is updated, the Lambda backing the Stream writes the
OLD_IMAGEto the table as a change control record. This enables pattern #2 by shipment and account.One virtue of this approach is that a single query operation can retrieve both the current shipment record and its full/partial change history in reverse order. This is the reason for the
x_prefixes on the current record's keys. A query with a key expression ofPK = shpmt#55abc AND SK >= "update", DESC sorting withScanIndexForward=Falseand a limit of 2 returns the current record (x_current) and the latest update record.* Whether this is a good solution for you also depends on expected read/write volumes.