I am trying to model the below table using DynamoDB:
A (String) | B (String) | C (String) | D (Integer) | E (Integer) | start_date (Timestamp) | end_date (Timestamp) where <A, B, C> columns uniquely define each tuple
The query pattern is: Give list of top 10 distinct C's with A equal to 'Y' ordered by D and E together where current time stamp is between start_date and end_date
For the above query pattern, one way to model the above table in dynamodb would be to have (A) as partition key and (D#E#B#C) as range key so that I would have records sorted by D and E on which I could apply the limits. We could model this in DDB as shown below:
A (partition key) | D#E#B#C (Sort key) | D | E | start_date | end_date
One more way to model this would be to combine D and E together to form a LSI in which case, the table would look like:
A (partition key) | B#C (Sort key) | D#E (LSI) | start_date | end_date
But the above design looks non-optimal because the sort key (B#C) doesn't actually serve any purpose other than just making tuple unique by combining with partition key (A). Having records sorted by (B#C) does not help much in my use-case.
In both the designs, modeling order by columns (D and E) looks bit ugly to me given the fact that I will have to convert Integers to Strings and concatenate them just because I have to include them under sort key or LSI. Also, limiting records based on "order by" clause is very important to me as I can't afford to return many records without limits during the network call from DB.
With relational database and SQL, the query would be SELECT DISTINCT TOP 10 C from table where A = 'value' and current_timestamp between start_date and end_date order by desc D, E with multi-column index on (start_date, end_date, D, E) and primary key (A, B, C). Multi-column index here is not a deal breaker for me because we have strict SLAs only for reads but not writes/updates/deletes.
Is there any better NoSQL modeling choice for the above table which could help in optimising the above query pattern? Would RDS be a better choice for modeling the above table?
