title: Hey #AskFirebase, how should I design this schema?
body: <p>I'm building an app in Firebase and could use some advice. Here are the key points:</p>
  
  <ul>
  <li>The app is for tracking time worked by employees.</li>
  <li>Users are divided into teams, with each team having a manager.</li>
  <li>As users clock in and out, the app tracks how long they work.</li>
  </ul>
  
  <p>I'm stuck on a design problem. Here are the requirements:</p>
  
  <ul>
  <li>Managers can read &amp; write the time cards for all users on their team, while users can only read &amp; write their own.</li>
  <li>When users are viewing their own time cards, the UI needs to show most recent time cards sorted by timestamp.</li>
  <li>When managers are viewing the time cards of everyone on their team, the UI needs to show most recent time cards sorted by timestamp.</li>
  </ul>
  
  <p>Solving for all 3 requirements simultaneously is proving to be a challenge. My first hunch was to structure the time cards like this:</p>
  
  <pre><code>{
    "timeCards": {
      "-teamId_1": {
        "-userId_1": {
          "-timeCard_1": {
            "startsAt: 1234567890123,
            "endsAt": 1234567899999,
            "duration": 2234567
          },
          "-timeCard_2": "..."
        }
      }
    }
  }
  </code></pre>
  
  <p>This solves the first two requirements easily:</p>
  
  <ul>
  <li>Easy to write the permission rules such that managers have read/write for the entire team, but users can only read/write their own.</li>
  <li>Easy to query a specific user's hours sorted by timestamp w/ <code>db.ref('timeCards/${tid}/${uid}').orderByChild('startsAt')</code></li>
  </ul>
  
  <p>Unfortunately, to solve the 3rd requirement with this schema requires some heavy lifting on the client. There is no way (that I'm aware of) to query Firebase for all of a team's time cards sorted by timestamp. So, I'd have to pull down all time cards for the team, then merge &amp; sort them on the client.</p>
  
  <p>I'm thinking this calls for denormalization, maybe a structure like this:</p>
  
  <pre><code>{
    "timeCards": {
      "byTeam": {
        "-teamId_1": {
          "-timeCard_1": {
            "userId": "-userId_1",
            "startsAt: 1234567890123,
            "endsAt": 1234567899999,
            "duration": 2234567
          },
          "-timeCard_2": "..."
        }
      },
      "byUser": {
        "-userId_1": {
          "-timeCard_1": {
            "teamId": "-teamId_1",
            "startsAt: 1234567890123,
            "endsAt": 1234567899999,
            "duration": 2234567
          },
          "-timeCard_2": "..."
        }
      }
    }
  }
  </code></pre>
  
  <p>But this gets complicated on the server, requiring two Firebase functions, one watching <code>timeCards/byTeam</code> and one watching <code>timeCards/byUser</code>, that each mirror the records into the other collection. </p>
  
  <p>I'm not sure how to avoid an infinite update loop in that situation, though. (Imagine updating <code>timeCards/byTeam/-teamId_1/-timeCard_1</code>, which fires the Firebase function and updates <code>timeCards/byUser/...</code>, which fires the Firebase function and updates <code>timeCards/byTeam/...</code>, etc.)</p>
  
  <p>Am I missing an easy solution?</p>