I use iView Media Pro for my photo management, and it supports tagging. Over the past several months I've been actively tagging new photos as I transfer them from my camera to my iMac. It was challenging coming up with a taxonomy that my wife and I could agree on; both in terms of depth and terminology. From a "people" standpoint, we resolved to use our families' first names, then bucket everyone else in as "Friends" or "Family." I've found a disturbing pattern in actually tagging however. With each transfer, I spend 2/3s of my time tagging the pics (one pass over all photos for each tag), and 1/3rd deleting the bad pics. That's a pretty poor management ratio if you ask me. No real surprise, but actually "tagging" my pics diligently is a real pain. Furthermore, if you're not going to tag 100% of your pics, then don't bother tagging at all. Reason being, if you want that pic that had your son and spouse in it, and it wasn't tagged to begin with, you're going to have to view all pics (maybe between some date range) to find it.
Tagging is an all or nothing deal I'm finding, and it's very time consuming. The value of having my data tagged is very high however, so I'll continue doing it; it's just painful.