Databases employ a number of universal concepts. One of these is the idea of hierarchy, an outline or indented list. It's found in the indented lists of XML. And it's found, generally, whether using computers or index cards.

Consider an example of not just a list of items, and one not just in a particular order, but with indentation, as well. This example, below, could be similar to the left-pane catalogs used at this very site for backgrounds, textures, photos, etc.

We'll say that - colors (not shown) - is a category not displayed in the final catalog. But in creating the output catalog, it has some purpose and is just another nested category. What you want to be able to do is add categories, at any point, at any level, delete categories, and move them around (including all subcategories). You can interactively use a 'treeview' listbox, with expandable and collapsible categories. You can use programming. Whatever you decide.

So how does one store this in order to modify it, as suggested? It would seem you'd have a record for each entry. How do you sort at each level, separately? Well, you could include just a single sort field, but just sort that for a single level of any particular subcategory. You could create a subselection, in SQL, a view, and sort that.

Now if you were convinced that the outline was pretty much set, and that all that would be added or deleted were particular entries/categories, none of which themselves had subcategories, then you could 'hard code' the thing into a number of relations, tables. Each table would be one level down. You'd have seven tables, in this example, which could be modified and read by all the existing methods and rules of the particular database system.

But if you thought some categories could be moved, or added, deleted to change the structure of the outline, then you'd have to dynamically create and recreate the tables, even every time you just move an item around, or add a new one. You could do that. But there's another alternative, which many just find more elegant, even if one loses the retrieval capabilities of a normalized relational database. And that's to include not just a sort field, but a one way link field for each entry to its immediately superior category. Because of that link field, the list, or rather as a database table, refers to itself. It's self-referential. Some people call it a 'self-join' table. I might call it a 'recursive table' (for the method used to identify superior categories), others a 'hierarchical table', or whatever else. And because of the convenience it might well be used. But such a self-joined table is a departure from relational practice, to take advantage of the convenience. And it is something of a dispute whether it should be used, ever, at all, because of this discrepancy.

Such a list is probably best referred to as an - adjacency list. For myself, I prefer to differentiate between, list, and sublist. I tend to call the flat, non-indented sections, a list, and the whole indented thing, or some indented portion of it, an outline, or hierarchy, table of contents, or the like. People generally call it a - tree. And in this case, the entries must be unique. You can't have the same Dark, for example, as a subcategory of both 'colors (not shown)' and Link. Uniquely dependent, rather.

Given that, these are basic operations used on any set of data - Modification, Addition, Deletion, and in the case of a sorted list, Relocation. In this case, modification refers to semantics, meaning. If one changed, Dark, to Lighter, by accident, then the subcategories are not properly subcategories of, Lighter. But verification might involve duplicating data from the Dark record, in each subcategory, which would further violate the rules of normalized relational design. So, in that sense, for purposes here, Modification is irrelevant. It's not checked.

That leaves - Addition, Deletion, and Relocation. Relocation is accomplished, as suggested, with a single field, used to sort each list within the whole. One might use a treeview to move things around, on the screen. Then the underlying tables have to be updated to reflect that new ordering, of course, and not merely just the treeview display itself. Remembering that each subcat, each entry, can point to only one superior category, resorting follows relocation. And relocation, for sake of convenience, can mean that one drags an item above, or below, at the same level or that adjacent, or else nowhere, relative to other entries. If it's nowhere, off the screen, convenience might have it that one intends to quickly move that entry to the very top level (or - as a convenience, it could be read as quick deletion, rather than pressing the DEL key).

Or you select a category and move to a new target. It might be convenient to place the selection above, before, the target, or just below, at the same level, but to pick just one method and not allow both. Similarly, as an option, it might be allowed to place above or below the item, but at the next level. Again, either above or below, but not both. You could place before, on the same level; and so, without the switch or option, in this example above, selecting and 'picking up' White, and then stopping at Blue just below, will place White above Blue, where the 'level order' for White becomes 1, and that for Tan becomes 0. Or, if using an option, you could place the selection in the list under the target; as draggng White to Blue would place it last in the list of Sky, Aqua, White, using such an option. If the option is only examined in the program to avoid amiguity at just the same level, and only that, then if you dragged Light to Red, it and everything under Light would be placed under Red. And then moving Light to colors (not shown) would place it back on the same level as Dark, after Dark, just as it was. In each case, relocating to another level is unambiguous and ignores any option, suggested here. There is the possibility of a fault, as well. If you place a category with subcats under one of those very subcats, you have a logical problem. So you might check for such a situation, before allowing a relocation to proceed!

As suggested, dragging off screen could be interpreted as a desire to delete the item. I would prefer pressing either a separate command button, and/or using the Delete key on the keyboard. Deletion suggests one of two possibilities. Either one deletes just the item, and moves any immediate subcats up to link with the deleted item's superior category, or I think it more intuitive that a deletion of an item, even with a host of subcategories, implies that the item and all subcategories are to be deleted. This is the designed behavior when one deletes from an ActiveX treeview control, by the way. If one didn't wish that, they could relocate those subcats, in some manner, before deleting the particular category/item. The deletion then simply involves decreasing the sort values by one, just for that list (the same level), starting at the item following that which was deleted.

Addition is particularly easy if you allow for subsequent relocation. That is, you can insist that new items go at the end of the list. As with relocation, you might use an option to insert at the same level as the last selected item, or below at the next level down. Or you might just insert a new item at the end of the top level list, and leave relocation to follow that.

CONTINUE