Database design: should nested objects have their own table?

Question

I'm somewhat new to database design, so I'd like some pointers on how best to lay my current tables out.

I have a table Jobs that holds various jobs. Users can create Subjobs. A Subjob has a Job as a parent. A Subjob has all the same properties as a Job, but some of them are read-only, whereas they are all read/write for a Job. A Job can have many Subjobs. At the moment, there may only be one layer of subjobs, but I'd like the flexibility to allow for infinite nesting of Subjobs in the future. The objects will be interacted with through a MVC web app.

I've considered two options for layout:

• Jobs and Subjobs each have their own table.
  • This seems like "good design" because I don't introduce columns in Job with the sole purpose of nesting with itself.
  • It's a bit of a pain for coding the web app, since a Job and Subjob would have to have two separate Controllers/sets of Views, despite them being identical in properties.
  • It makes less sense from a design perspective if infinite nesting is introduced.
• Jobs and Subjobs are on the same table. Jobs are just given a nullable parent_job_id property that is non-null if it is a Subjob.
  • Makes sense for infinite nesting.
  • Less of a pain for coding the web app.
  • A weird nesting property is introduced to the Job table that has nothing to do with the actual properties of a Job.

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.

Answer 1

The first option is fine if you were not describing a hierarchy with multiple levels, but you are. The pattern you are describing is commonly known as an Adjacency List which is stored as you describe your second option.

Some other options for storing a hierarchy are:

• Nested sets (more complicated implementation but potentially faster queries without recursion).
• Materialized Path (Stores a character representation of the hierarchy path, e.g. like a file storage system)
• Modifications / Helper tables for Adjacency List (Flat table, bridge table)
• Custom implementations like HierarchyId

Hierarchy Reference:

• StackOverflow - What are the options for storing hierarchical data in a relational-database
• Louis Davidson - Presentations - How to Optimize a Hierarchy In SQL Server - Presentations & Demo Code