I have a DynamoDB structure as following.
- I have patients with patient information stored in its documents.
- I have claims with claim information stored in its documents.
- I have payments with payment information stored in its documents.
- Every claim belongs to a patient. A patient can have one or more claims.
- Every payment belongs to a patient. A patient can have one or more payments.
I created only one DynamoDB table since all of aws dynamodb documentations indicates using only one table if possible is the best solution. So I end up with following :
In this table ID is the partition key and EntryType is the sortkey. Every claim and payment holds its owner. My access patterns are as following :
- Listing all patients in the DB with pagination with patients sorted on creation dates.
- Listing all claims in the DB with pagination with claims sorted on creation dates.
- Listing all payments in the DB with pagination with payments sorted on creation dates.
- Listing claims of a particular patient.
- Listing payments of a particular patient.
I can achieve these with two global secondary indexes. I can list patients, claims and payments sorted by their creation date by using a GSI with EntryType as a partition key and CreationDate as a sort key. Also I can list a patient's claims and payments by using another GSI with EntryType partition key and OwnerID sort key.
My problem is this approach brings me only sorting with creation date. My patients and claims have much more attributes (around 25 each) and I need to sort them according to each of their attribute as well. But there is a limit on Amazon DynamoDB that every table can have at most 20 GSI. So I tried creating GSI's on the fly (dynamically upon the request) but that also ended very inefficiently since it copies the items to another partition to create a GSI (as far as I know). So what is the best solution to sort patients by their patient name, claims by their claim description and any other fields they have?