Before going full digital, I mostly used Apple iPhoto to store and more or less organize (meaning I just store them really) all my digital pictures (taken with the Canon P&S camera) and then when I did develop a film, I used to ask for a CD containing the JPEGs of these film pictures. As I did almost no editing on these, it worked quite well for some years. JPEGs are not the best format for real editing anyway.
After buying my Pentax K10D, it was pretty obvious I needed something more sophisticated, especially in the light for my rather early choice of shooting almost only in RAW on the K10D. RAW shooting makes it much easier to edit & correct pictures (especially highlights/shadow and white balance), things that are much more complicated with JPEGs. There are several interesting software to do this (DxO, Pentax's own supplied software) but they are all concentrating on only one aspect: editing. Photo management is much more than that: you need way to sort pictures, manage keywords and so on.
Being a mac fan, there was more or less only two choices: the first one being Apple's own software, Aperture. Aperture is very nice, works well especially on the management side but requires a very fast machine to be really efficient.
The second choice came a few months after the first one when Adobe released beta versions of Lightroom, their own photo management software.
After testing both and especially the final retail version of Lightroom, I choose it. At 208 € (early price till end of June, 2007), it is expensive but very worth it IMHO. I used it to completely reorganize my library, put proper keywords and I'm very happy with it.
It can import pictures while converting them to Digital Negatives (DNG, see on Adobe's site), something I saw as a "universal" RAW format, put keywords while importing and all sorts of things you do with digital pictures (editing, crop, noise reduction, spot removal, and so on.).
The 1.1 update brought in some much needed features like better noise management, the new clarify (smart sharpening) tool and much better photo management through catalogs.
Recommended!
After a few months of public beta testing (which I didn't really follow for lack of time), Lightroom 2.0 is now out and I just upgraded my copy (Adobe still has its own "special" rate between the US dollar and the Euro though :-( ).
There are several changes in there and it seems that Adobe manage to keep the lead compared to Aperture. While Aperture got a major release a few months ago ahead of Adobe, with several new things (mostly about what is called plugins but really more external editors), Adobe has done something I did not really expect: adding localized changes like Aperture but as non-destructrive ones! That means that 1. one does not leave the workflow while doing local changes and 2. it does not add a TIFF or PSD file along with the original image! That also means that these changes are part of the history and you can still do snapshots, go back and all that!
With releases 3 and 4 of Lightroom, Adobe updated the RAW rendering engine twice and it is now much better then ever. The one in 3.x (2010 process) was good but much slower then 2.x while speed came back with release 4 (2012 process).