title: Best Way to Store Hierarchal Data (Parent <- Child <- Grandchild)
body: <p>I have a dataset that I need to work with that represents a part schematic for a large machine. I need to come up with an appropriate database schema for this dataset and am having trouble coming up with something to use that represents this data efficiently. </p>
  
  <p>The top level components are the biggest "structures", and as you traverse down the hierarchy, the data represents inner components, or components that make up the inner components. For example, at the top level, there could be an engine as a level 1 component, and then a level 2 component is a piston, which goes into an engine, and a level 3 component could be a gasket that goes into the piston. </p>
  
  <p>This representation is spread across a few hundred lines of a CSV file. There are 3 columns for IDs: </p>
  
  <ul>
  <li>a <code>master_id</code>, which all components have</li>
  <li>a <code>parent_id</code>, which all components have as well but their value varies based on the situation. 
  
  <ul>
  <li>If the component in question is a level 1 part, the <code>parent_id</code> is its own master_id. </li>
  <li>If the component in question is a level 2 part, the <code>parent_id</code> is the <code>master_id</code> of the level 1 component. </li>
  <li>If the component in question is a level 3 part, the <code>parent_id</code> is the <code>master_id</code> of the level 2 component. </li>
  </ul></li>
  </ul>
  
  <p>Basically, the parent id of any component is the master id of the component in the level above it. So lv1 parent is lv1 master (since it'  s the root), lv2 parent is lv1 master, and lv3 is lv2 master. Also, multiple components can share a parent ID, meaning multiple lv2 parts, for example, can have the same parent ID.</p>
  
  <ul>
  <li>a <code>grandparent_id</code>, which only level 3 components have (but not all lv3 components for some reason (idk I didn't make this data set)). If a component is lv3 and has a <code>grandparent_id</code>, the grandparent ID is a direct link back to the master ID of the lv1 component. Yeah, confusing right? </li>
  </ul>
  
  <p>So here's an example. A lv3 component has a <code>master_id</code> of 700000137, a <code>parent_id</code> of 600000049, and a <code>grandparent_id</code> of 500000006. If we look at the component with a <em>master</em> of 600000049, we'll see that this is a lv2 component that has a parent id of 500000006, which is the <em>master</em> id of a lv1 component, and again is the grandparent of this lv3 component.</p>
  
  <p>I prefaced this post saying I need to come up with a database representation for this data set (it has later use in a project but the data organization is the first step). I'm comfortable using PostgreSQL, so my initial thoughts were to make 3 tables, master, parent, and grandparent, where based on the key that I'm parsing out, I would insert this into the appropriate database and foreign key back to the other tables if there were parent or grandparent keys. But I realized this could get quite hairy especially since there could be multiple foreign keys linking back to a single master id, and I feel with this representation some data could possibly get repeated, which I obviously don't want happening. </p>
  
  <p>My second thought was to use something like a python dictionary, where I essentially build out a tree like structure where the lv1 components are in the top level, the lv2 components in the second, etc. I could then convert the dictionary into JSON, since Python is nice that way, and store that json blob in the database. But, this JSON blob could potentially get REALLY big, though I guess that's just something I'd have to live with as the dataset grows. This part schematic I was given is only for one machine, so basically each entry in my database would be like</p>
  
  <pre><code>id | name      | json
  ----------------------
  1  | machine_a | JSON_BLOB_MACHINE_A
  ----------------------
  2  | machine_b | JSON_BLOB_MACHINE_B
  
  etc...
  </code></pre>
  
  <p>does my second approach seem better than trying to create separate tables that represent each part level and foreign keying back to parents? If there's a better way to do this with Postgres, I'd appreciate you explaining it. Otherwise, I'm probably going to go with the latter route. Thanks!</p>