I'm creating a report builder-like application with the intent on making an extremely novice friendly front end.
The back end of the application will be managed by developers who can build a 'report model' which will specify which tables, fields and joins to include for the end user to use.
I am also looking to add functionality that will not require a report model. My application will scan the target SQL database, and created a virtual model with all of the joins and fields mapped.
After this i will need to be able to generate the most 'logical' or efficient a path between tables e.g with the minimal amount of joins. Kind of similar to the traveling salesman scenario.
I have decided to go about this by using tree's to map all joins from a specific table which will be the start node, and all other tables it could possibly connect to. This way i can do a breadth-first tree traversal to in theory find the most 'logical' path to join.
My issue with this is that not all databases will be set up in a particularly machine logic friendly manner. Meaning that a human may see a logical join because of specific table or field names, that my algorithm may not. (Below is a simple iteration of my algorithm in c# that does not yet record the path between tables)
public Node<T> findClosestObjToNode(Node<T> node, T obj)
{
Boolean matchFound = false;
List<Node<T>> toSearch = new List<Node<T>>();
List<Node<T>> toSearchNext = new List<Node<T>>();
toSearchNext.Add(node); //add proimary node to search list
while(!matchFound){
toSearch.AddRange(toSearchNext); //copy the searchnext list to search
toSearchNext.Clear();
foreach(Node<T> _node in toSearch){
if (node.contains(obj)) //check for existance of object in the nodes children
return node.getChild(obj); //return the child node that contains the object
else
foreach (Node<T> cNode in node.getChildren()) //no matching object found in child nodes
toSearchNext.Add(cNode); //so add each child node to the list of nodes to search
}
if(toSearchNext.Count == 0) //no match found
return null;
}
return null;
}
My question is really. Does the way i have planned above seem like a decent solution to the whole problem, or is there a better way of doing the above to get more accurate table joins.
