title: What is the best way to relate rows in the same table
body: <p>I'm trying to design a table of rows, each row can relate in some manner to another row.</p>
  
  <p>On the practical level we are talking about layers of soil (archaeology).</p>
  
  <p>One row - one layer - is above another layer, essentially A is above B is above C, equally C is below B is below A. In a graph database this would be very trivial, unfortunately we are using a relational database. Each relation ship has a direction, A is above B, but B is not above A. The data will be added as A is above B although we may want to search and find what is above B, with or without being explicit about this relationship, if A is above B then B must be below A. A further complication is that D could equal E, as in it is the same as, so D=E and E=D, however this is not directional because the relationship is true both ways.</p>
  
  <p>So that's the logic.</p>
  
  <p>But, what is the best way to design some tables to allow this? I should note we use PostgreSQL and Django.</p>
  
  <p>A basic example follows.</p>
  
  <pre><code>Table name: Layer
  id
  Layer_name
  </code></pre>
  
  <p>I assume we need a second table like the following:</p>
  
  <pre><code>Table name: Relationship
  id
  layer_id1
  relationship
  layer_id2
  </code></pre>
  
  <p>I have also seen the use of the REFERENCES for non-directional relationships, as stated <a href="https://dba.stackexchange.com/questions/48568/how-to-relate-two-rows-in-the-same-table">here</a>. </p>
  
  <p>I mention we are using Django, can/should the frontend do any sanitisation or tricks like if statements, if A is above B then also enter B is below A, if A=B or B=A only enter A equals B.</p>
  
  <p>Any constructive advice is welcome.</p>