Friday, September 5, 2008

Folders in a Database

If you're using folders and storing them in a database, the conventional approach is to use recursion for things like folder validation, moves and membership.

Using recursion with a database like this is usually pretty bad performance wise, consider using a token instead. A token could be, for example, a folder id string field that keeps the path of the folder using the identifiers of all its parents.

So, if you had folders "a/b/c", the folder id string for the "c" object could be "/100/101/" representing the path to this folder.

In application code, you should obviously never manipulate this string directly, the string should be completely private to your inner most classes that represent your data model.

Using this technique however, it becomes very simple to answer some very basic questions like:
  • Can I drop this folder into this other folder? Simple, just check if the string starts with the same path or is equal. So "c" can be dropped into "a" or "b", but "a" cannot be dropped in "a" or "b" because the path for "c" contains "a".
  • How many items are contained in this folder? Just check how many items have a matching string.
  • Makes it easy to find siblings, all the items found in the same folder have the same folder id/path.
If you do keep using recursion, make sure your code is secure at least, don't allow for an infinite number of recursions.

No comments: