Data structures using Interbase SQL

I am trying to implement a data structure that accomplishes the following:

Root -               A
Child           B1          B2
Child        C1   C2 C3     C4       C5
Child      D1  D2 D3     D4 D5 D6    D7

A has B1 and B2 as children
B1 has C1, C2 and C3 as children
B2 has C4, C5 as children
C1 has D1 and D2 as children
C2 has D3 as child
C4 has D4, D5 and D6 as children
C5 has D7 as child.

I am using an SQL database to implement this. Note that the number of children in each node is not specific. It could be 1 to n.  I would like some souce code to implement this:

1. I want to be able to find out how many children each node has up to x generations. For example:
Generation(A,2)=B1,C1,C2,C3,B2,C4,C5
Generation(B1,2)=C1,D1,D2,C2,D3

2. How many children are immediately under a specific node? For example:
Children(A) =B1,B2
Children(B1)=C1,C2,C3

3. How many children are totally under a specific node? For example:
AllChildren(B1)=C1,D1,D2,C2,D3,C3
AllChildren(B2)=C4,D4,D5,D6
AllChildren(C1)=D1,D2

I want the actual source code Interbase SQL and Delphi 5.0 code ( no controls for Enterprise, only up to Professional)

okemotoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

okemotoAuthor Commented:
As soon as possible.
0
kretzschmarCommented:
hi okemoto,

could you show your datastructure?
it should like

CREATE TABLE People (
  ID NUMBER(9, 0) NOT NULL,
  NAME VARCHAR2(24) NOT NULL,
  REMARK VARCHAR2(48),
  PARENT_ID NUMBER(9, 0)
)

meikl
0
okemotoAuthor Commented:
I do not have a specific data structure in mind but I would like to get the best solution. Can you suggest one?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

kretzschmarCommented:
hi okemoto,

well, i've done a little sample-app,
its independent from the database, therefore you can use interbase or paradox or whatever.

unit db_childs_u;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Grids, DBGrids, Db, DBTables;

type
  TForm1 = class(TForm)
    Table1: TTable;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    function CountChildren(Parent : Integer) : Integer;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

//Table-structure named People
//ID->Integer->Autoincrement
//ParentID->Integer
//Name->Char(WhateverlengthNeeded)
//here can be more fields

//append as child of current selected record
procedure TForm1.Button1Click(Sender: TObject);
var ID : Integer;
begin
  If (Table1.State = dsBrowse) and
      not (Table1.IsEmpty) then
  Begin
    ID := Table1.FieldByName('ID').AsInteger;
    Table1.Append;
    Table1.FieldByName('ParentID').AsInteger := ID;
  end;
end;

//post - save changes
procedure TForm1.Button2Click(Sender: TObject);
begin
  if (Table1.State = dsEdit) or
     (Table1.State = dsInsert) then Table1.Post;
end;

//Count children of current Record
procedure TForm1.Button3Click(Sender: TObject);
begin
  ShowMessage(table1.FieldByName('Name').AsString+' has '+
              inttostr(CountChildren(table1.FieldByName('Id').AsInteger))+
              ' Children and ChildCildren');
end;

//recursive counting
function TForm1.CountChildren(Parent : Integer) : Integer;
var
  q : TQuery;
begin
  Result := 0;
  q := TQuery.Create(Self);
  q.DatabaseName := Table1.DataBaseName;
  q.SQL.Clear;
  q.SQL.Add('Select * from People where ParentId = '+IntToStr(Parent));
  q.Open;
  q.First;
  while not q.Eof do
  Begin
    Result := Result+CountChildren(q.FieldByName('ID').AsInteger)+1;
    q.Next;
  end;
  q.Close;
  q.Free;
end;

//Count children of current Record
procedure TForm1.Button3Click(Sender: TObject);
begin
  ShowMessage(table1.FieldByName('Name').AsString+' has '+
              inttostr(CountChildren(table1.FieldByName('Id').AsInteger))+
              ' Children and ChildChildren');
end;

end.

meikl
0
kretzschmarCommented:
hi again,

its a solution to point 3.
other follows
(just a change in the
CountChildren-Function)

meikl
0
kretzschmarCommented:
ok, here the change


//recursive counting
function TForm1.CountChildren(Parent,MaxGenarations : Integer) : Integer;
var
  q : TQuery;
begin
  Result := 0;
  q := TQuery.Create(Self);
  q.DatabaseName := Table1.DataBaseName;
  q.SQL.Clear;
  q.SQL.Add('Select * from People where ParentId = '+IntToStr(Parent));
  q.Open;
  q.First;
  while not q.Eof do
  Begin
    if MaxGenarations <> 0 then
      Result := Result+CountChildren(q.FieldByName('ID').AsInteger,MaxGenarations-1);
    Result := Result + 1;
    q.Next;
  end;
  q.Close;
  q.Free;
end;

//Count children of current Record
procedure TForm1.Button3Click(Sender: TObject);
begin
  //All Childs-> MaxGenerations-Param is -1
  ShowMessage(table1.FieldByName('Name').AsString+' has '+
              inttostr(CountChildren(table1.FieldByName('Id').AsInteger,-1))+
              ' Children');
  //Childs of one generation->MaxGenerations-Param is "wanted genetionsdepth" -1 := 0
  ShowMessage(table1.FieldByName('Name').AsString+' has '+
              inttostr(CountChildren(table1.FieldByName('Id').AsInteger,0))+
              ' Children');
  //Childs of two generation->MaxGenerations-Param is "wanted genetionsdepth "-1 := 1
  ShowMessage(table1.FieldByName('Name').AsString+' has '+
              inttostr(CountChildren(table1.FieldByName('Id').AsInteger,1))+
              ' Children');
end;

meikl
0
kretzschmarCommented:
hi okemoto,

this is just a preventive-lock to prevent that you can delete this q.
be free to reject, if my advise does
not match your needs.

meikl ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RadlerCommented:
Listening...
0
okemotoAuthor Commented:
meikl,

I provided three questions. You answered the second one. What about the first and third? I am not trying to be picky but I just want a complete solution.
Thanks.



 

 
 
0
kretzschmarCommented:
hi okemoto,

i thought i had answered all three
based on the last version of the
function TForm1.CountChildren(Parent,MaxGenarations : Integer) : Integer;

 
//Count children of current Record
procedure TForm1.Button3Click(Sender: TObject);
begin

//that call to 3.
//All Childs-> MaxGenerations-Param is -1
ShowMessage(table1.FieldByName('Name').AsString+' has '+
inttostr(CountChildren(table1.FieldByName('Id').AsInteger,-1))+
' Children');

//that call to 2.
//Childs of one generation->MaxGenerations-Param is "wanted genetionsdepth" -1 := 0
ShowMessage(table1.FieldByName('Name').AsString+' has '+
inttostr(CountChildren(table1.FieldByName('Id').AsInteger,0))+
' Children');

//that call to 1.
//Childs of two generation->MaxGenerations-Param is "wanted
genetionsdepth "-1 := 1
ShowMessage(table1.FieldByName('Name').AsString+' has '+
inttostr(CountChildren(table1.FieldByName('Id').AsInteger,1))+
' Children');

end;

because you can pass now a MaxGenerationdepth-paramater:
is the paramater -1 -> all childs are counted of current record
is the paramater 0 -> one child-generation is counted of current record
is the paramater 1 -> two child-generations are counted of current record
is the paramater 2 -> three child-generations are counted of current record
is the paramater 3 -> four child-generations are counted of current record
...and so on

did i missed something ?

meikl
0
okemotoAuthor Commented:
I will try the solution and if it meets the criteria, you got it!
Will let you know.
Thanks!.
0
okemotoAuthor Commented:
meikl,

I tried your solution and I have a comment regarding the performance. I used it with the data I posted for the question and even with such little data I noticed that the performance was an issue. I am concerned if for example I am working with 4~5,000 records. This will be evidently noticeable. Any improvements on your code?
0
kretzschmarCommented:
hi okemoto,

first, yes, it will take time as every counting or calculation (just go into the explorer->rightclick on a dir->select propertys and look how the explorer counted the byteamount of the dir)

well have done a test to all three parts with your given data and get the result in <0.5 sec for each.

yes,
there are performance-improvements possible by code:

- generally to minimize the datatransfer, the query can changed into:
q.SQL.Add('Select Id from People where ParentId = '+IntToStr(Parent));
- if the maxgenarations parameter is 0, then a codebranch can be added like
If MaxGenarations = 0 then
begin
  q.SQL.Add('Select count(Id) as CId from People where ParentId = '+IntToStr(Parent));
  q.Open;
  if q.eof then Result := 0
  else Result := q.FieldByName(CId).AsInteger;
end
else . . .

and yes, there also performance-improvements (code-independent) in configuration-settings and os-settings (caching) also in the used hardware (highend)

and yes, there are also improvements on sql-based serversystems->let the server count->stored procedure/function

as you see, are there some performance influences, which are code independent, used hardware, used databasesystem, OS-Settings, used databasedriver, concurrent tasks on server/workstation, network and so on.

meikl
0
kretzschmarCommented:
hi again,

this will be the changes for code:

//recursive counting
function TForm1.CountChildren(Parent,MaxGenarations : Integer) : Integer;
var
  q : TQuery;
begin
  Result := 0;
  q := TQuery.Create(Self);
  q.DatabaseName := Table1.DataBaseName;
  q.SQL.Clear;
  if MaxGenarations = 0 then
  begin
    q.SQL.Add('Select Count(Id) as CId from People where ParentId = '+IntToStr(Parent));
    q.Open;
    If q.Eof then Result := 0
    else Result := q.FieldByName('CId').AsInteger;
  end
  else
  begin
    q.SQL.Add('Select Id from People where ParentId = '+IntToStr(Parent));
    q.Open;
    q.First;
    while not q.Eof do
    Begin
      // if MaxGenarations <> 0 then  //never used
      Result := Result+CountChildren(q.FieldByName('ID').AsInteger,MaxGenarations-1);
      Result := Result + 1;
      q.Next;
    end;
  end;
  q.Close;
  q.Free;
end;

meikl
0
okemotoAuthor Commented:
Good! You deserve the points!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.