I needed this type of lens for a few projects & they've been great. It's true that Photoshop CS5 introduced beautiful adjustment tools that can help adjust many of the lens distortion, but I personally like to do most work optically and only use photoshop for minor work.
The lens (regardless of manufacturer) has two pivot points, one to tilt & one to shift. Each for a different application, and first let's examine the tilting.
When tilting (like angular leaning) the lens, you're controlling the depth of field. In the following example, I shot a telephone and on purpose stretched its cable across the table at a short distance. Aperture was at f/13, but without the tilt mechanism I couldn't have gotten that much in focus (the crops are at 100%).
Artistically, you can also add depth of field when it does not exist, like this example and it makes objects appear as if they were miniature
The shifting (sliding sideways) mechanism helps reduce the lens wide distortion. In the following, I used a 24mm lens, and removed the wide angle lens distortion optically with the shift.