Generating table dependencies using templates

[Georg Wrede]

Aarti wrote:
> Hello!
> 
> In my program I need to generate dependencies between database tables, 
> so I will always get information about below mentioned relations for 
> every specific table:
> 1. parent tables
> 2. child tables
> 3. ancestor tables
> 4. descendant tables

I'm not sure whether this is posted in order to find a solution for the 
problem, or to find information on best practices with templates.

Since Don already started to tackle the latter, I'll merely mention how 
I'd resolve the former.

In general, with database tables and this kind of problem, I'd resort to 
recursive lookups. I'd have an extra table that contains the fields 
TABLE, ANCESTOR. (If there is a huge number of tables, then of course 
one could have this table indexed on both columns, but in practice 
neither index is needed because in real-life applications the total 
number of tables is "small".)

To find out whether table A is an ancestor to table B, one calls a 
function that checks for all "parents" to table B, and if A is found, 
then return A. If parents are found but not A, then check their parents. 
Conversely if one needs to know if A is a descendant.