"You will find it usually between a DCE (data communication equipment, ie modem) and a DTE (data terminating equipment ie PC or other serial terminal)."

In fact, that is the only application for which RS232 was specifically designed; the full title of the document is:
"Interface Between Data Terminal Equipment and Data Circuit-Terminating Equipment Employing Serial Binary Data Interchange"

Of course, once the standard was available, people started applying it to other applications - particularly communications between devices without the use of modems.
And that's where the trouble started.
Because people were too lazy to actually read the spec [1], they made false assumptions; eg, some computer manufacturers assumed that, because the computer is a DTE, everything else must be a DCE...

So we now have the mass of subtly (and not-so-subtly) differently wired cables needed to connect any two pieces of "RS232" equipment!

[1] Of course, the standardisation bodies (ANSI, TIA, BSI, ETSI, et al) don't help by charging a small fortune for their publications - but that's an old rant:
http://www.8052.com/forum/read.phtml?id=20522
http://www.8052.com/forum/read.phtml?id=20381