Do you mean table count or row count?
Perhaps you could explain the context of this problem a bit more. Is this work? homework? The context might help people feel more comfortable giving you detailed answers. From your description it could be work ("I have the requirement of maintaining") but the way it is expressed ("Storing a tree structure") sounds a bit like the kind of homework question a database professor might ask if he is trying to help you transition from programming data structures to their relational database equivalent.
In programming data structures trees are often represented using a parent node with a list of children. In relational databases the storage locations are reversed. The goal in a relational database is to store everything in tables where there is one and only one value for each combination of table column and row. In other words, the database table is something like a spreadsheet with exactly one value in each cell, but much more efficient.
The most straightforward way of implementing a tree structure in a database is to use foreign key columns. A foreign key column is a database table column that stores the id of a row in a database table, either the current table or some other table. To construct a tree, you set up a database table where one of the columns stores a foreign key. Each record in the table represents a node in the tree. When you create the row for that tree node, you store the parent of the tree node in the foreign key column.
In a programming data structure you navigate from parent to child by visiting each of the children. You start with a list of top tree nodes, visit each node, get the list of their children and visit each of those children. But how do you do that in a database? Remember, each row only knows its parent.
First of all, you will need a convention for marking the rows that are top nodes. Logically speaking these are the rows that have no parent, so you might use NULL when you have no parent. To find all of the top nodes, write an SQL query to find all the nodes with a NULL value in that foreign key column discussed earlier. Then to get all of the children of node X, you write another query that selects all of the nodes where the foreign key id equals the row id of node X. Then for each of the rows returned by that query (lets say X1, X2, X3), you write yet more queries: one to retrieve every node where the parent is X1, another to retrieve every node where the parent is X2, and so on. You repeat this query writing process until you find a node with no children.
More precisely, you write a program to repeat this query writing process. SQL doesn't have a syntax for repeating a query until it returns no results (though it has been discussed and there are published proposals) so this repetitive construction of queries is normally done in a stored procedure or in a language with good database libraries, for example Perl :-).
It helps a lot if you make sure your database table has a single column as primary key. That way you have a very simple row id which you can use in your queries. You also need to be very careful that your sequence of queries don't cycle. If, for example, A is a child of B which is a child of C which is a child of A, then the queries "all children of A", followed by "all children of B", followed by "all children of C" would turn into an infinite loop when you tried to visit all of the children of C and get their children. One of those children would be "A" and would lead to the creation of the query "all children of A" which is where you started.
What I have described is the simplest case. There are much more complicated ways to implement tree structures in databases. For example, you could have a tree where nodes can belong to any one of three different database tables, depending on the type of object stored in each node. The idea is similar (using foreign keys to store parents), but the queries to retrieve the children may be much more complicated. Hopefully, if you are just starting with relational databases, you won't need such a complicated tree. If you do need something that complicated, please feel free to post again if you are using Perl on this project. Next time, it would be a good idea to post some code showing what you tried so far.
Best, beth
In reply to Re: tree structure of database
by ELISHEVA
in thread tree structure of database
by ramkamath
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |