title: DynamoDB Table Design: How do I model a one to many relationship where I need all of the "one" items and one of "many" sorted by some attribute
body: <p>I've spent my entire career working with denormalized relational databases. I am having a hard time un-learning all of that in order to implement a single-table design that can handle a couple specific access patterns on an &quot;App Store&quot;-like personal project.</p>
  <p><a href="https://i.stack.imgur.com/nzoiB.png" rel="nofollow noreferrer">Here's a quick ERD.</a> There is an App model identified by a platform (iOS, Android) and bundle identifier along with a Defaults map that is used when creating new versions. Each App can have 0 to many Versions which are identified by a version number (which is a sequential numerical value and is unique within the context of an App).  A version has an IsReleased attribute along with several others (like Name, Release Notes, Binary Path, etc).</p>
  <p><strong>Access Patterns</strong></p>
  <ol>
  <li>List the latest version of every app.</li>
  <li>List the latest version of every app for a given platform.</li>
  <li>List the latest version of every app where IsReleased is 1.</li>
  <li>List the latest version of every app for a given platform where IsReleased is 1.</li>
  <li>Get the latest version of a specific app.</li>
  <li>Get the latest version of a specific app where IsReleased is 1.</li>
  <li>Get all versions of a specific app.</li>
  <li>Get all versions of a specific app where IsReleased is 1.</li>
  <li>Get the Default attribute for a specific app.</li>
  </ol>
  <p>I'm having trouble with 1 though 4, this table is where I was headed.  I'm having a hard time coming with a GSIs that will give me the all of the <em>app</em> items with a single <em>version</em> by sort order.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>pk</th>
  <th>sk</th>
  <th>Defaults</th>
  <th>App Name</th>
  <th>Version</th>
  <th>IsReleased</th>
  <th>Other Attributes</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td><code>app_ios_com.app.one</code></td>
  <td><code>defaults</code></td>
  <td><code>{ ... json ... }</code></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td><code>app_ios_com.app.one</code></td>
  <td><code>version_1</code></td>
  <td></td>
  <td>App One</td>
  <td>1</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td><code>app_ios_com.app.one</code></td>
  <td><code>version_2</code></td>
  <td></td>
  <td>App One</td>
  <td>2</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td><code>app_ios_com.app.one</code></td>
  <td><code>version_3</code></td>
  <td></td>
  <td>App One</td>
  <td>3</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td><code>app_ios_com.app.two</code></td>
  <td><code>defaults</code></td>
  <td><code>{ ... json ... }</code></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td><code>app_ios_com.app.two</code></td>
  <td><code>version_1</code></td>
  <td></td>
  <td>App Two</td>
  <td>1</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td><code>app_ios_com.app.two</code></td>
  <td><code>version_2</code></td>
  <td></td>
  <td>App Two</td>
  <td>2</td>
  <td>0</td>
  <td></td>
  </tr>
  <tr>
  <td><code>app_ios_com.app.two</code></td>
  <td><code>version_3</code></td>
  <td></td>
  <td>App Two</td>
  <td>3</td>
  <td>0</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>For example, for access pattern 1, I want:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>pk</th>
  <th>sk</th>
  <th>Defaults</th>
  <th>App Name</th>
  <th>Version</th>
  <th>IsReleased</th>
  <th>Other Attributes</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td><code>app_ios_com.app.one</code></td>
  <td><code>version_3</code></td>
  <td></td>
  <td>App One</td>
  <td>3</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td><code>app_ios_com.app.two</code></td>
  <td><code>version_3</code></td>
  <td></td>
  <td>App Two</td>
  <td>3</td>
  <td>0</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>For example, for access pattern 3, I would want:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>pk</th>
  <th>sk</th>
  <th>Defaults</th>
  <th>App Name</th>
  <th>Version</th>
  <th>IsReleased</th>
  <th>Other Attributes</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td><code>app_ios_com.app.one</code></td>
  <td><code>version_2</code></td>
  <td></td>
  <td>App One</td>
  <td>3</td>
  <td>1</td>
  <td></td>
  </tr>
  <tr>
  <td><code>app_ios_com.app.two</code></td>
  <td><code>version_1</code></td>
  <td></td>
  <td>App Two</td>
  <td>1</td>
  <td>1</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Some data constraints that I have to keep in mind:</p>
  <ul>
  <li>There are currently only 10 to 20 apps, but I need to be able to support hundreds</li>
  <li>Most apps will have 100 to 200 versions with 20 to 30 released versions.  The biggest app has 1000 versions of which 50 are released.</li>
  <li>In the back-end, the IsReleased flag will typically be toggled from 0 to 1, but will occasionally be toggled from 0 to 1.</li>
  <li>The average version item is approximately 2 KB.</li>
  <li>The access pattern variations where IsReleased is 1 are more frequently used by a significant margin.</li>
  </ul>
  <p>I feel like the solution is right in front of me, but I can't put my finger on it.</p>