11.5: Composite sort keys with hierarchical data
In the last two strategies, we saw some data with a couple levels of hierarchy—an Organization has Users, which create Tickets. But what if you have more than two levels of hierarchy? You don’t want to keep adding secondary indexes to enable arbitrary levels of fetching throughout your hierarchy.
A common example in this area is around location-based data. Let’s keep with our workplace theme and imagine you’re tracking all the locations of Starbucks around the world. You want to be able to filter Starbucks locations on arbitrary geographic levels—by country, by state, by city, or by zip code.
We could solve this problem by using a composite sort key. The term composite sort key means that we’ll be smashing a bunch of properties together in our sort key to allow for different search granularity.
| Primary Key | Attributes | ||||
|---|---|---|---|---|---|
| Partition key: Country | Sort key: STATE#CITY#ZIP | StreetAddress | SquareFeet | ||
| "USA" | "NE#OMAHA#68118" | "#100 St Andrews lane" | "921" | ||
| "USA" | "NY#NEWYORKCITY#10001" | "#675 6th Ave" | "1211" | ||
| "USA" | "NY#NEWYORKCITY#10019" | "1500 Broadway" | "1924" | ||
| "FRANCE" | "ILE-DE-FRANCE#PARIS#75001" | "26 Avenue de I'Opera" | "2102" | ||
In our table, the partition key is the country where the Starbucks is located. For the sort key, we include the State, City, and ZipCode, with each level separated by a #. With this pattern, we can search at four levels of granularity using just our primary key!
The patterns are:
1: Find all locations in a given country. Use a Query with a key condition expression of PK = <Country>, where Country is the country you want.
2: Find all locations in a given country and state. Use a Query with a condition expression of PK = <Country> AND begins_with(SK, '<State>#').
3: Find all locations in a given country, state, and city. Use a Query with a condition expression of PK = <Country> AND begins_with(SK, '<State>#<City>').
4: Find all locations in a given country, state, city, and zip code. Use a Query with a condition expression of PK = <Country> AND begins_with(SK, '<State>#<City>#<ZipCode>').
It works best when:
1: You have many levels of hierarchy (>2), and you have access patterns for different levels within the hierarchy.
2: When searching at a particular level in the hierarchy, you want all subitems in that level rather than just the items in that level.