Nested data in a database table
I have a table that contains records that are related like this:
parent, child, grandson.
I would like to be able to run queries to do the following:
- get all the records that came down from a particular record.
- get all records that are direct children of a particular record (i.e. only direct children)
- get all records that are grandchildren of a particular record (i.e., only grandchildren).
- get grandparents records
- get the parent of the entry
- restore all records (and their children and grandchildren).
I want this query to return data in a sorted way, so I can create a structured document (like XML from a query result). By "sort" I mean that the result set is "GROUPED" by the parent THEN child, THEN grandchild
I designed a simple table like this (PSEUDO CODE):
CREATE TABLE family_tree {id integer
, parent_id integer
, child_id integer
, fname varchar(16)
, lname varchar(32) };
I have a number of questions:
-
(Considering the queries I want to run [(1) - (6) above]) is this the best (ie most efficient table structure I can use?) If not, what is
-
Can anyone help me write ANSI SQL statements to execute queries (1) - (6) above?
source to share
Check out the site tutorial .
- The recursive storage method (id, parent_id) allows you to retrieve the direct descendants of a particular node.
- The pre-ordered tree traversal method allows you to get the entire branch of a specific node.
So, it's best to use columns id,parent_id,left,right
. This tutorial contains all the code, but the SQL queries should be clear enough.
You should be able to infer all queries from the ones given in the tutorial.
source to share
I see you have already accepted Eimantas' answer by saying that
"So your personal, parent, left, right columns are your best bet."
I want to explicitly quote the following quote from the quoted link (italics mine):
"Adding Node
How do we add a tree node to a tree? There are two approaches: you can store the parent column in your table and simply rerun the rebuild_tree () function - a simple but not as elegant function; or you can update the left and right values of all nodes on the right side of the new node. "
source to share