Re: Modelling hierarchy in the relational database

On Fri, 17 Aug 2001 21:08:17 GMT, abracad_at_nospam.com (abracad) wrote:

>I am constructing a database to model hierarchy, e.g.
> A
> |
 B
> |
> C
>
>I envisage a table parent consisiting fields parent, child and would
>store records A,B and B,C. Would I also need to store record A,C?
>
>I.e. can SQL act recursively to determina all the children of a given
>node (could it retrieve B and C if I queried for the children of A
>with only records A,B and B,C stored?)
>
>Many thanks in advance for any help on this

I have some test code which will do this at:-

www.bestfitsoftware.com.au/downloads/access/recursion.zip

In the table which contains the records you want to drill down on, you need to include a field (Yes/No) which indicates whether the record is the parent in a structure. (In your example A & B would be tagged as parents, C would not).

Your second table (structures) would consist of ParentID, ChildID and any other data associated with the structure (cost, qty etc).

The code loops through all records in the structure table associated with the selected record, for each record it checks the main table to see if that item is itself marked as parent, and if so calls itself recursively until the bottom level has been reached.

The example uses the treeview control, so you may need to set a reference to mscomctl.ocx

Hope this helps. Received on Sat Aug 18 2001 - 01:10:17 CEST