nested lists - What is the most efficient way of storing complex, self-referencing tree structures in an SQL database? -

i'm aware of , have used 2 methods in past basic tree structures: adjacency lists , nested sets. understand several pros , cons these approaches - e.g., adjacency lists quick update slow query, nested sets opposite (slow update, quick query).

however, require ability store more complex tree-like structures. best way describe using human family relationships. first thought each element have both "ancestors" tree , "descendants" tree. however, there significant redudancy in approach as, using example below, both cameron , kelly share of bob's ancestor tree (and updates more time consuming, insertion tree have insert multiple trees). second thought include tree references. e.g., alice has own ancestor tree. both (4,5) element coming cameron's ancestor tree, , (2,3) element coming kelly's ancestor tree reference alice's ancestor tree. second approach requires less data storage, experience faster updates (only update single tree vs. multiple trees) , retain speed advantages of querying large tree structure (although sql query such self-referencing nested set rather complicated). however, drawback second approach data becomes "fragmented" (much inodes on hard drive).

 = alice                                    b = bob                                      c = cameron                                  j = john                                     k = kelly                                    +------------------------------------------+    +-----------+            +-------------+     |(2) bob (3)|            |(4) alice (5)|     +-----+-----+            +------+------+           |                         |                  |                         |                  |                         |                  |                         |                  |    +---------------+    |                  +----+(1) cameron (6)+----+                       +---------------+                  +-------------+            +------------+    |(2) alice (3)|            |(4) john (5)|    +-----+-------+            +-------+----+          |                            |               |                            |               |                            |               |                            |               |      +-------------+       |               +------+(1) kelly (6)+-------+                      +-------------+                +------------------------------------------+             +-------------+                              |(1) alice (6)+----------+                   +-+-----------+          |                     |                      |                     |                      |                     |                      |           +---------+-----+        +-------+-----+     |(2) cameron (3)|        |(4) kelly (5)|     +---------------+        +-------------+  

for second approach, visualising multiple nested sets stacked in front of 1 another, nodes "drawing line" along z-index node on plane.

note above example - i'm not storing human relationships, storing complex tree data. there many reasons store such complex hierarchical structures, i'll let imagination run wild!

the question: what efficient way performance wise (updates , selects) of storing complex, self-referencing tree structures in sql database? i'm referring postgresql, if have alternatives (even sql itself), i'm open hearing well.

in oracle can achieve using start .. connect by. known hierarchical queries.

for example:

select *   <table_name>   start <parent_column_name> null  connect prior <child_column_name> = <parent_column_name>;