If you don't mind using the mouse, you can bring a single window to the foreground by right-clicking the corresponding application's Dock icon and selecting it from the list.

Another more visual option (that you can manage with keyboard only) is to use Exposé. In Snow Leopard, press the Exposé key on the keyboard (or set your own shortcut), start typing the name of the window you want, and press return to bring only that window to the foreground.

I've found these to be pretty good built-in solutions to the single-window problem.