My brother Randy and I have been exchanging emails more frequently than usual lately, mostly because he's been bitten by the photography bug and decided to go "all in" and get a Canon 40D with some great lenses. Since I share this interest and also have invested in a Canon system, I pointed him toward some lenses and some professional photography forums, and (among many of his friends who are dSLR fiends) have talked with him about the photo editing workflow options.
When I decided to get into this hobby, I guess I didn't really appreciate how central a good editing system is. A note about editing: I'm not someone who tries to use editing software to make my photos look supersaturated or hyper-real, though there's certainly a market out there for that kind of thing. But digital photography (well, any photography, really) has its limits, and the sensor can only record a certain amount of data, whereas our eyes sees much more dynamic range of color and tone. I see photo editing as a way to make your pictures more closely resemble the scene you saw. Some scenes almost always need some help in the digital darkroom, namely sunsets, sunrises, and other situations where you tend to have one part of your picture overexposed and the other part underexposed.
For two and a half years now, I've been using CS2 Photoshop for all of my photo editing needs. A lot happens with photo software in two and a half years, I've discovered, and I've been contemplating upgrading to CS4 for a while. And then Adobe introduced the newest version of Lightroom. I knew a lot of pro photographers use Adobe's Lightroom as their primary editing software (they use CS4 for fine detail), and when Randy asked my opinion on it, I realized it might be worth a demo (you can download a demo for free) to see how a Lightroom + CS2 setup might compare to a straight CS4 upgrade. I think Lightroom is pretty intuitive, whereas Photoshop requires you to learn a whole language of photoediting ("layers" and "masks" and "filters"...). And you can really do a lot in Lightroom very quickly so long as you don't require extreme precision in your editing. So I've put together my own Lightroom workflow example for Randy (and anyone else who cares!). The screen shots end up pretty small, but just click on the images to see them bigger.
Take this image: this was from our recently blogged trip to the tidepools.

I had taken a couple of shots with different exposures, but this was a good middle-of-the-road kind of shot. The rocks and Nick are underexposed but not terribly so and the sky isn't completely overexposed, so I knew I could tease out some detail from both halves of the shot.
Lightroom has a gradient tool that digitally resembles a graduated neutral density filter. By clicking on the gradient tool, I can choose a lighter exposure (+0.90) and drag a gradient near the horizon. Because of the rocks, I angle it (but still have a patch of sky that accidentally gets lightened - I can fix that later).

I can click on the gradient tool icon again and the lines disappear. A control point remains, however, so if I want to change anything about the gradient, I can just click on the control point and re-edit.

Now the sky: I click on the gradient tool and click "New," select an exposure of -0.70, and drag a new gradient from the sky toward the rocks, overlapping slightly.

By clicking on the toggle just above the "-0.70" you reveal more adjustments to be made within that gradient, and I bump up the contrast and the saturation a bit in that gradient area (the sky).

Now to address that halo caused by using a gradient on an irregular surface. Next to the gradient tool is a brush tool. Clicking on the brush opens up some options, including brush size, feather diameter (like a fade of the effect), flow (how opaque you want your change to be), and density. I set a small diameter and middle range on the other options. Because I want the brush to correct the increased exposure from the Nick+rocks gradient, I make the brush correction a -1.10 exposure. I click the letter O on the keyboard to show my brushstrokes in red, and then I paint with the brush (I can click the letter O again to hide the red and show the actual correction).

Still not completely satisfied with the results, I click on the gradient tool and it brings up the control points for the two gradients I made before. I click on the "Nick and rocks" gradient and change the angle a bit to reduce the halo effect. I also increase the exposure correction. I could go back and use the brush on the upper part of the cliff if I wanted to, but I'm past the point of caring at this point (since this is just for demonstration).

Going down to the global adjustments tools, I increase the saturation and contrast of the entire image, and I could also now sharpen the image (though I prefer to sharpen selectively in Photoshop).

If you go back up and look at the original image, you can see how dramatic these changes are, and they bring the image on my screen closer to what I saw through my camera viewfinder.
BUT ... what if I only used Photoshop? Here's what I would do:I open the raw image in Photoshop's raw camera utility (the blue areas represent underexposed parts of the image that have been pushed or "clipped" to black):

I can recover the detail in the clipped black areas by increasing the overall exposure of the image a bit (+0.35; I also move the shadows slider back to 0 to help recover detail in the darker areas). At this point, I don't increase the exposure too much, as I will want to darken the sky and lighten the rocks yet.

I click Open and open the image in Photoshop. I select the sky using the magic wand tool, and by Shift+clicking, I can add to the selection area until the whole sky is selected. Zoomed in to 50%, you can see that this is pretty exact.

I go to Layer--> New Adjustment Layer--> Levels and the Levels dialog pops up. If you look down in the lower right, you can see that a new layer appears above the "Background" layer in the palette. There's an icon to represent a "levels" adjustment and next to it is the layer mask. The layer mask represents, in black and white, the parts of your image being affected by the layer: black is unaffected and white is affected. Since I had selected only the sky, the rest of the image is black on the layer mask. I moved the left (shadows) slider to the right until it touched the very start of the black levels curve, and I moved the right (highlights) slider to the left a bit. I moved the middle (midtones) slider to the right a bit as well. That takes care of the sky, for the most part.

Instead of using the magic wand or another technique to select the rest of the image, I go to Select--> Reselect and the selection of the sky that I had just made reappears. I can Select Inverse to select Nick and the rocks. I feather this selection (Select--> Feather--> 50 pixels) so that there's no dramatic line of demarkation, then I go to Layers--> New Adjustment Layer--> Curves. The Curves dialog pops up. If you look in the palette (lower right), you see the Curves 1 layer appear, with a layer mask that has the rocks and Nick in white.

Here, I want to improve overall lightness without increasing the maximum bright point or the minimum dark point, so Curves was a logical choice. By dragging up and to the left, I lighten the selected area.

Now I want to add contrast. I could just find the contrast slider and increase it (like in Lightroom) but I prefer to use the Curves tool to adjust contrast. I click on a whitecap in the water and an open circle appears on the Curve representing the tonal value of that point in the image.

Because I want the whitecaps to be lighter, I know I need to adjust this part of the curve up and to the left.

Similarly, I want to adjust the darker water and the rocks and make them a bit darker (but still preserve the detail). I find the appropriate spot on the curve and pull it down a bit.

I can now use duplicate background layers with layer masks to adjust sharpness to, say, just the rocks and Nick or remove noise just in the darker parts of the rocks.
The final picture is pretty similar to the Lightroom final pic but without the halos caused by the gradient tools. Sharpening in Lightroom is pretty crude, though there are plug-ins (ranging from inexpensive to very expensive) that do a much better job than the in-house Lightroom sharpening; sharpening in Photoshop is much more exact and offers much more control. With enough patience and practice, using the gradient / brush tools in Lightroom can produce a very similar effect to PS's layers technique. Both programs and techniques are non-destructive; that is, both produces changes that are infinitely re-editable and at any point you can throw away some or all of the changes you've made without damaging the original image.
I'm pretty happy with Lightroom and PS and a coordinated workflow. I'm hoping that Lightroom introduces more control over Curves, because for me it's a dealbreaker of a tool - I can use it for Contrast and also color correction (by adjusting curves for each of the color channels). A school of philosophy on contrast that was pioneered by Ansel Adams is the Zone Method, in which you identify and categorize your tonal range (ie the histogram in the upper right) by "zones." And then to add contrast, you apply changes to specific zones and leave others alone, providing an image that is dramatic but realistic. This is done very easily using the histogram as your visual Zone reference and Curves to adjust at the ideal place. I rely heavily on this tool!! If Lightroom made a Curves tool more similar to that in Photoshop, I would probably rarely end up using PS.
Food for thought, RJ. =) Miss you, bro!