title: Database design: should nested objects have their own table?
body: <p>I'm somewhat new to database design, so I'd like some pointers on how best to lay my current tables out.</p>
  
  <p>I have a table <code>Jobs</code> that holds various jobs. Users can create <code>Subjobs</code>. A <code>Subjob</code> has a <code>Job</code> as a parent. A <code>Subjob</code> has all the same properties as a <code>Job</code>, but some of them are read-only, whereas they are all read/write for a <code>Job</code>. A <code>Job</code> can have many <code>Subjobs</code>. At the moment, there may only be one layer of subjobs, but I'd like the flexibility to allow for infinite nesting of <code>Subjobs</code> in the future. The objects will be interacted with through a MVC web app.</p>
  
  <p>I've considered two options for layout:</p>
  
  <ul>
  <li><code>Jobs</code> and <code>Subjobs</code> each have their own table.
  
  <ul>
  <li>This seems like "good design" because I don't introduce columns in <code>Job</code> with the sole purpose of nesting with itself.</li>
  <li>It's a bit of a pain for coding the web app, since a <code>Job</code> and <code>Subjob</code> would have to have two separate Controllers/sets of Views, despite them being identical in properties.</li>
  <li>It makes less sense from a design perspective if infinite nesting is introduced.</li>
  </ul></li>
  <li><code>Jobs</code> and <code>Subjobs</code> are on the same table. <code>Jobs</code> are just given a nullable <code>parent_job_id</code> property that is non-null if it is a <code>Subjob</code>.
  
  <ul>
  <li>Makes sense for infinite nesting.</li>
  <li>Less of a pain for coding the web app.</li>
  <li>A weird nesting property is introduced to the <code>Job</code> table that has nothing to do with the actual properties of a <code>Job</code>.</li>
  </ul></li>
  </ul>
  
  <p>Any advice on how to handle this? Are there additional design patterns I haven't considered? I'm using Entity Framework 6 Code First, if that matters.</p>