Search code examples
nosqlamazon-dynamodbdata-modelingdynamodb-queriesamazon-dynamodb-data-modeling

Query children of One-To-Many Relationship based on date along with parent

I have two entities in my dynamo table: User and Order.

Each user has 0..* orders and each order has exactly one associated user. Every order also has a orderDate attribute, that describes when the order was placed. My current table is structured as follows to make retrieving all orders for a specific user efficient:

+--------------+----------------+--------------------------------------+
| PK           | SK             |              Attributes              |
+--------------+----------------+-------------+-----------+------------+
|              |                | name        | firstName | birthDate  |
+--------------+----------------+-------------+-----------+------------+
| USER#userid1 | META#userid1   | Foo         | Bar       | 2000-10-10 |
+--------------+----------------+-------------+-----------+------------+
|              |                | orderDate   |           |            |
+--------------+----------------+-------------+-----------+------------+
| USER#userid1 | ORDER#orderid1 | 2020-05-10  |           |            |
+--------------+----------------+-------------+-----------+------------+

I now have a second access pattern where I want to query all orders (regardless of user) that were placed on a specific day (e.g. 2020-05-10) along with the the user(s) that placed them.

I'm struggling to handle this access pattern in my table design. Neither GSIs nor different primary keys seem to work here, because I either have to duplicate every user item for each day or I can't query the orders together with the user.

Is there an elegant solution to my problem?

Solution

  • This is a perfect use case for a secondary index. Here's one way to do it:

    You could create a secondary index (GSI1) on the Order item with a Partition Key (GSI1PK) of ORDERS#<orderDate> and a Sort Key (GSI1SK) of USER#<user_id>. It would look something like this:

    User Orders

    The logical view of your GSI1 would look like this:

    enter image description here

    GSI1 would now support a query of all orders placed on a specific day.

    Keep in mind that denormalizing your data model (e.g. repeating user info in the Order item) is a common pattern utilized in DynamoDB data modeling. Remember, space is cheap! More importantly, you are pre-joining your data to support your applications access patterns. In this instance, I'd add whatever User metadata you need to the Order item so it gets projected into the index.

    Make sense?