title: MongoDB Database Structure and Best Practices Help
body: <p>I'm in the process of developing Route Tracking/Optimization software for my refuse collection company and would like some feedback on my current data structure/situation.</p>
  
  <p>Here is a simplified version of my MongoDB structure:</p>
  
  <p><strong>Database:</strong> data</p>
  
  <p><strong>Collections:</strong></p>
  
  <p><strong>“customers”</strong> - data collection containing all customer data.</p>
  
  <pre><code>  [
      {
          "cust_id": "1001",
          "name": "Customer 1",
          "address": "123 Fake St",
          "city": "Boston"
      },
      {
          "cust_id": "1002",
          "name": "Customer 2",
          "address": "123 Real St",
          "city": "Boston"
          },
      {
          "cust_id": "1003",
          "name": "Customer 3",
          "address": "12 Elm St",
          "city": "Boston"
      },
      {
          "cust_id": "1004",
          "name": "Customer 4",
          "address": "16 Union St",
          "city": "Boston"
          },
      {
          "cust_id": "1005",
          "name": "Customer 5",
          "address": "13 Massachusetts Ave",
          "city": "Boston"
      }, { ... }, { ... }, ...
  ]
  </code></pre>
  
  <p><strong>“trucks”</strong> - data collection containing all truck data.</p>
  
  <pre><code>[
      {
          "truckid": "21",
          "type": "Refuse",
          "year": "2011",
          "make": "Mack",
          "model": "TerraPro Cabover",
          "body": "Mcneilus Rear Loader XC",
          "capacity": "25 cubic yards"
      },
      {
          "truckid": "22",
          "type": "Refuse",
          "year": "2009",
          "make": "Mack",
          "model": "TerraPro Cabover",
          "body": "Mcneilus Rear Loader XC",
          "capacity": "25 cubic yards"
      },
      {
          "truckid": "12",
          "type": "Dump",
          "year": "2006",
          "make": "Chevrolet",
          "model": "C3500 HD",
          "body": "Rugby Hydraulic Dump",
          "capacity": "15 cubic yards"
      }
  ]
  </code></pre>
  
  <p><strong>“drivers”</strong> - data collection containing all driver data.</p>
  
  <pre><code>  [
      {
          "driverid": "1234",
          "name": "John Doe"
      },
      {
          "driverid": "4321",
          "name": "Jack Smith"
      },
      {
          "driverid": "3421",
          "name": "Don Johnson"
      }
  ]
  </code></pre>
  
  <p><strong>“route-lists”</strong> - data collection containing all predetermined route lists.</p>
  
  <pre><code>   [
      {
          "route_name": "monday_1",
          "day": "monday",
          "truck": "21",
          "stops": [
              {
                  "cust_id": "1001"
              },
              {
                  "cust_id": "1010"
              },
              {
                  "cust_id": "1002"
              }
          ]
      },
      {
          "route_name": "friday_1",
          "day": "friday",
          "truck": "12",
          "stops": [
              {
                  "cust_id": "1003"
              },
              {
                  "cust_id": "1004"
              },
              {
                  "cust_id": "1012"
              }
          ]
      }
  ]
  </code></pre>
  
  <p><strong>"routes"</strong> - data collections containing data for all active and completed routes.</p>
  
  <pre><code>[
      {
          "routeid": "1",
          "route_name": "monday1",
          "start_time": "04:31 AM",
          "status": "active",
          "stops": [
              {
                  "customerid": "1001",
                  "status": "complete",
                  "start_time": "04:45 AM",
                  "finish_time": "04:48 AM",
                  "elapsed_time": "3"
              },
              {
                  "customerid": "1010",
                  "status": "complete",
                  "start_time": "04:50 AM",
                  "finish_time": "04:52 AM",
                  "elapsed_time": "2"
              },
              {
                  "customerid": "1002",
                  "status": "incomplete",
                  "start_time": "",
                  "finish_time": "",
                  "elapsed_time": ""
              },
              {
                  "customerid": "1005",
                  "status": "incomplete",
                  "start_time": "",
                  "finish_time": "",
                  "elapsed_time": ""
              }
          ]
      }
  ]
  </code></pre>
  
  <p>Here is the process thus far:</p>
  
  <p>Each day drivers begin by Starting a New Route. Before starting a new route drivers must first input data:</p>
  
  <ol>
  <li><strong>driverid</strong></li>
  <li><strong>date</strong></li>
  <li><strong>truck</strong></li>
  </ol>
  
  <p>Once all data is entered correctly the Start a New Route will begin:</p>
  
  <ol>
  <li>Create new object in collection <strong>“routes”</strong> </li>
  <li>Query collection <strong>“route-lists</strong>” for <strong>“day”</strong> + <strong>“truck”</strong> match and return <strong>"stops"</strong></li>
  <li>Insert <strong>“route-lists”</strong> data into <strong>“routes”</strong> collection</li>
  </ol>
  
  <p>As driver proceeds with his daily stops/tasks the <strong>“routes”</strong> collection will update accordingly.</p>
  
  <p>On completion of all tasks the driver will then have the ability to Complete the Route Process by simply changing “status” field to “active” from “complete” in the "routes" collection.</p>
  
  <p>That about sums it up. Any feedback, opinions, comments, links, optimization tactics are greatly appreciated.</p>
  
  <p>Thanks in advance for your time.</p>