title: Proper way to make a relation between multiple rows of single table
body: <p>I've got following situation: I want to connect multiple records from one table with some kind of relation. Record could have no connection to other, or could have multiple of them (1 or more). There is no hierarchy in this relation. </p>
  
  <p>For example:</p>
  
  <pre><code>CREATE TABLE x
  (
    x_id SERIAL NOT NULL PRIMARY KEY,
    data VARCHAR(10) NOT NULL
  );
  </code></pre>
  
  <p>I've thought of two ideas:</p>
  
  <ol>
  <li>Make a new column in this table, which will contain some relationId. It won't reference anything. When new record is inserted, I will generate new relationId and put it there. If I would want to connect other record with this one, I will simply put the same relationId.</li>
  </ol>
  
  <p>Example:</p>
  
  <pre><code>CREATE TABLE x
  (
    x_id NUMBER(19, 0) NOT NULL PRIMARY KEY,
    data VARCHAR(10) NOT NULL,
    relation_id NUMBER(19, 0) NOT NULL
  );
  
  insert into x values (nextval, 'blah', 1);
  insert into x values (nextval, 'blah2', 1);
  </code></pre>
  
  <p>It will connect these two rows.</p>
  
  <p>pros:</p>
  
  <ul>
  <li>very easy</li>
  <li>easy queries to get all records connected to particular record</li>
  <li>no overhead</li>
  </ul>
  
  <p>cons:</p>
  
  <ul>
  <li><p>hibernate entity will contain only relationId, no collection of
  related records (or maybe it's possible somehow?)</p>
  
  <ol start="2">
  <li>Make a separate join table, and connect rows with many-to-many relation. Join table would contain two column with ids, so one entry would connect two rows.</li>
  </ol></li>
  </ul>
  
  <p>Example:</p>
  
  <pre><code>CREATE TABLE x
  (
    x_id SERIAL NOT NULL PRIMARY KEY,
    data VARCHAR(10) NOT NULL
  );
  
  CREATE TABLE bridge_x
  (
    x_id1 NUMBER(19, 0) NOT NULL REFERENCES x (x_id),
    x_id2 NUMBER(19, 0) NOT NULL REFERENCES x (x_id),
    PRIMARY KEY(x_id1, x_id2)
  );
  
  insert into x values (1, 'blah');
  insert into x values (2, 'blah2');
  
  insert into bridge_x values (1, 2);
  insert into bridge_x values (2, 1);
  </code></pre>
  
  <p>pros:</p>
  
  <ul>
  <li>normalized relation</li>
  <li>easy hibernate entity mapping, with collection containing related
  records</li>
  </ul>
  
  <p>cons:</p>
  
  <ul>
  <li>overhead (with multiple connected rows, every pair must be inserted)</li>
  </ul>
  
  <p>What is the best way to do this? Is there any other way than these two?</p>