title: DynamoDB primary key and indexes table design
body: <p>I am trying to wrap my head around how I should design a table in DynamoDB. I need a <code>Subscription</code> table that stores items for each subscription a company has. So I was planning on the following attributes.</p>
  
  <ul>
  <li>SubscriptionID</li>
  <li>CompanyID</li>
  <li>StartDate</li>
  <li>EndDate</li>
  <li>ProductID</li>
  </ul>
  
  <p>I have read quite a lot about how indexing works in DynamoDB in regards to secondary indexes, but I really struggle to make sense of it all. It seems as if the more I read, the more confused I get. I understand the core concepts of global secondary indexes and local secondary indexes as well as hash key + range key. The problem is putting it all together and designing a table that supports my query requirements.</p>
  
  <p>As of now, I have the following query requirements:</p>
  
  <ol>
  <li>Being able to query for active subscriptions for a given company. That is, querying by <code>CompanyID</code> and comparing <code>StartDate</code> and <code>EndDate</code> to a given date</li>
  <li>The same as the above, except querying to see if a given company has an active subscription for a specific <code>ProductID</code></li>
  </ol>
  
  <p>In the future (or now), I would like to be able to query for all active subscriptions for a given <code>ProductID</code>. My initial plan was to use <code>SubscriptionID</code> as the hash key and the <code>CompanyID</code> as a range key for efficient queries for a given company's subscriptions. However, I am not sure if it is even possible to query for the range key alone? I know that the combination of the hash key and range key is unique (composite key), but do I always need to include the hash key when performing lookups for the range key?</p>
  
  <p>I was also thinking to make local secondary indexes for <code>StartDate</code> and <code>EndDate</code> to efficiently query for a given company's active subscriptions, but I am not sure if I should make these global secondary indexes instead for when I want to query for all of the active subscriptions (for all companies)? My guess would be yes, because I need to query across all partitions and not just the one for a single company.</p>
  
  <p>Like I mentioned, I have a fundamental understanding of the concepts, but my struggle is when it comes to querying tables/indexes and which query patterns each combination of primary keys and global/local secondary indexes make possible/impossible. I find it hard to grasp the combination of a key-value store with the added capabilities that secondary indexes provide. I would really love if anyone could give me an example of how they would design the table and explain why, such as how the various choices support my particular query requirements.</p>
  
  <p>I am aware that much of this stuff is documented, but I am struggling to use this information in relation to this scenario. I hope anyone can help. <strong>So:</strong> how would you design the table to support the queries that I listed and why?</p>