11.3: Composite primary key + the Query API action
The next strategy to model one-to-many relationships—and probably the most common way—is to use a composite primary key plus the Query API to fetch an object and its related sub- objects.
Item collections are all the items in a table or secondary index that share the same partition key. When using the Query API action, you can fetch multiple items within a single item collection.
The table below shows some example items:
| Primary Key | Attributes | ||||||
|---|---|---|---|---|---|---|---|
| Partition key: pk | Sort key: sk | OrgName | PlanType | UserName | UserType | ||
| "ORG#MICROSOFT" | "METADATA#MICROSOFT" | "Microsoft" | "Enterprise" | ||||
| "ORG#MICROSOFT" | "USER#BILLGATES" | "Bill Gates" | "Member" | ||||
| "ORG#MICROSOFT" | "USER#SATYANADELLA" | "Satya Nadella" | "Admin" | ||||
| "ORG#AMAZON" | "METADATA#AMAZON" | "Amazon" | "Pro" | ||||
| "ORG#AMAZON" | "USER#JEFFBEZOS" | "Jeff Bezos" | "Admin" | ||||
Notice how there are two different item types in the item collection. We have an Organization item type and User item type.
This primary key design makes it easy to solve four access patterns:
1: Retrieve an Organization. Use the GetItem API call and the Organization’s name to make a request for the item with a PK of ORG#<OrgName> and an SK of METADATA#<OrgName>.
2: Retrieve an Organization and all Users within the Organization. Use the Query API action with a key condition expression of PK = ORG#<OrgName>. This would retrieve the Organization and all Users within it, as they all have the same partition key.
3: Retrieve only the Users within an Organization. Use the Query API action with a key condition expression of PK = ORG#<OrgName> AND begins_with(SK, "USER#"). The use of the begins_with() function allows us to retrieve only the Users without fetching the Organization object as well.
4: Retrieve a specific User. If you know both the Organization name and the User’s username, you can use the GetItem API call with a PK of ORG#<OrgName> and an SK of USER#<Username> to fetch the User item.