In order to check efficiency and performance, I have compared how each model performed with respect to the following two problems:
To find all the units under the command of the French general Dominique Vandamme (a top-down query), if possible as an indented report (which requires keeping track of the depth within the tree) or as a simple list. Note that in all cases we have an index on the commander's name. I refer to this problem as the Vandamme query.
To find, for all regiments of Scottish Highlanders, the
various units they belong to, once again with and without proper
indentation (a bottom-up query). We have no index on the names of
description in the
tables), and our only way to spot Scottish Highlanders is to look
Highland string in the
name of the unit, which of course means a full scan in the absence
of any full-text indexing. I refer to this problem as the
To ensure that the only variation from test to test was in the model used, my comparisons are all done using the same DBMS, namely Oracle.
In the Vandamme query, we start with the commander of the French Third Corps, General Vandamme, and want to display in an orderly fashion all units under his command. We don't want a simple list: the structure of the army corps must be clear, as the corps is made of divisions that are themselves made of brigades that are themselves usually composed of two regiments.