Representing multiply-linked lists in SQL

I have a data structure consisting of a set of objects which are arranged into a multiply-linked list which is also (isomorphically) a valid DAG. It could be viewed as one single multiply-linked list, or as a series of n doubly-linked lists which may share members. (This is the same data structure from Algorithm for quic开发者_开发百科kly obtaining a partial ordering over multiple linked lists, for those of you following my questions.)

I am looking for a general technique, in no specific SQL dialect, for expressing this multiply-linked list/DAG in SQL, such that it's easy to take a given node and obtain:

• The previous and next links in the DAG, given a topological ordering of the DAG
• The previous and next links in each doubly-linked list to which this node belongs

Using the example data from that other question:

first  = [a, b,    d,    f,    h, i];
second = [a, b, c,       f, g,    i];
third  = [a,          e, f, g, h, i];

I'd want to be able to, given node f, obtain [(c|d|e), g] from the overall DAG's topology and also {first: [d, h], second: [c, g], third: [e, g]} from each of the lists orderings.

Here's the fun part: n, the number of doubly-linked lists, is not fixed and may grow at any time. I'd rather not redo the schema each time that happens.

All of the algorithms I've come up with so far either (a) stuff a big pickle into the DB and pull it out in order to calculate orderings, or (b) require that the lists be explicitly enumerated as recursive relations in the DB.

I'll go with an option in (b) if I can't find something better but I'm hoping that there's something magical out there to make this easier.

Pre: This is a question and answer forum, not 'lets sit down, group think for a bit, and solve the whole problem' forum.

I think what you want to investigate in a technique called 'modified preordered tree traversal' a mouthful i know, but it allows the storing of hierarchical data in a flat database and individual enties. Sadly, you do have to do some rewriting on inserts, but the selects can be done in a single query, so it's best for 'many view/ few changes' situations like a website. Luckily, you rarely have to rewrite the whole dataset (only the parts you changed and those hierarchically after them.

I remember a good article on the basics on it ( a couple years ago) but can't find the bookmark atm, so start with just a google search.

EDIT/UPDATE:

link: http://www.sitepoint.com/hierarchical-data-database/

No matter what, from dealing with this issue extensively, you will have to choose were to put the brunt of the work, on view, or on change. Depending on the size of the 'master' tree, you may (like me) decide to break the tree up into parts and use a tree of trees, limiting the update cost.