Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>What's the best way to represent a hierachy in a SQL database? A generic, portable technique?</p> <p>Let's assume the hierachy is mostly read, but isn't completely static. Let's say it's a family tree.</p> <p>Here's how not to do it:</p> <pre><code>create table person ( person_id integer autoincrement primary key, name varchar(255) not null, dob date, mother integer, father integer ); </code></pre> <p>And inserting data like this: </p> <pre><code>person_id name dob mother father 1 Pops 1900/1/1 null null 2 Grandma 1903/2/4 null null 3 Dad 1925/4/2 2 1 4 Uncle Kev 1927/3/3 2 1 5 Cuz Dave 1953/7/8 null 4 6 Billy 1954/8/1 null 3 </code></pre> <p>Instead, split your nodes and your relationships into two tables.</p> <pre><code>create table person ( person_id integer autoincrement primary key, name varchar(255) not null, dob date ); create table ancestor ( ancestor_id integer, descendant_id integer, distance integer ); </code></pre> <p>Data is created like this:</p> <pre><code>person_id name dob 1 Pops 1900/1/1 2 Grandma 1903/2/4 3 Dad 1925/4/2 4 Uncle Kev 1927/3/3 5 Cuz Dave 1953/7/8 6 Billy 1954/8/1 ancestor_id descendant_id distance 1 1 0 2 2 0 3 3 0 4 4 0 5 5 0 6 6 0 1 3 1 2 3 1 1 4 1 2 4 1 1 5 2 2 5 2 4 5 1 1 6 2 2 6 2 3 6 1 </code></pre> <p>you can now run arbitary queries that don't involve joining the table back on itself, which would happen if you have the heirachy relationship in the same row as the node.</p> <p>Who has grandparents?</p> <pre><code>select * from person where person_id in (select descendant_id from ancestor where distance=2); </code></pre> <p>All your descendants:</p> <pre><code>select * from person where person_id in (select descendant_id from ancestor where ancestor_id=1 and distance&gt;0); </code></pre> <p>Who are uncles? </p> <pre><code>select decendant_id uncle from ancestor where distance=1 and ancestor_id in (select ancestor_id from ancestor where distance=2 and not exists (select ancestor_id from ancestor where distance=1 and ancestor_id=uncle) ) </code></pre> <p>You avoid all the problems of joining a table to itself via subqueries, a common limitation is 16 subsuqeries.</p> <p>Trouble is, maintaining the ancestor table is kind of hard - best done with a stored procedure.</p>
    singulars
    1. This table or related slice is empty.
    1. POSQL - How to store and navigate hierarchies?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJosh
    1. USJosh
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFine for some purposes, and I agree that nodes and edges should be kept in separate tables - but tracking how *every* node connects to *every* descendent/ancestor will not scale well for large, complex graphs - what's presented is a denormalized (for 'distance' & 'vertical' connectivity) optimization that may not be appropriate for most. See Telcontar's answer.
      singulars
      1. PO
      1. USbacar
    2. COThis is my favorite way to model hierarchies until the data gets huge. It is simple and very flexible.
      singulars
      1. PO
      1. USAndrew
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload