While I'm sure this kind of thread has been opened before, I was specifically asked this question 3 times while streaming the other day. I figured it wouldn't hurt to go ahead and have something exact to point people towards, as the wiki isn't terribly descriptive as to the actual setup.
NOTES: This is intended for relatively new players; you need to know how to use mechanisms, digging, channeling, and carving fortifications to drain water or have another mass water removal option. These pictures were taken like 3 years into this fort. I've been attacked every 2-3 seasons since embark so I'm having some trouble keeping up with all the crazy that's going on... all the same I managed to get it dug and working. Ignore the idler count (in siege throws it off until they get reassigned), terrible stair layout (haven't set up the quickfort and too lazy to make sure everything is perfect), and other oddities outside of the cistern. If you have any questions please ask in the thread so that others can benefit from the answer as well. Feel free to PM me but don't expect an answer any time soon -- I only have a couple days a week to play and I spent most of that time in game. If you think you see a bug where water isn't properly being pressurized into the hospital cistern - you're right... it's already posted to the mantis and doesn't actually change the effectiveness of the overall construction.
Legend:
RED: Standard portion of this design
ORANGE: Additional requirements if you're inviting !Fun!
YELLOW: Totally optional... but you've been warned.
Getting started: If you have a river or brook you're already set; If you've got ocean it'll work with some minor changes; If you embarked with only a few pools on the map good luck (try building you're fort UNDER the cavern layer with water and use that instead). Assuming you have a good starting fort, create the cistern area with the river channels known but not dug -- you don't want to be trying to dig the cistern while racing the 20 seconds it'll take for the water to hit your miners. The top layer should be something like this at the end:
If you are worried about creatures breaking your floodgates (or are trying to avoid !Fun!) use a bridge underneath instead of floodgates. When raised it can't be destroyed by building destroyers from the opposite direction it raises towards. You will want to either floor over or build a bridge over the area you will channel - but you may want to hold off on that until everything else is working.
Keep in mind this will NOT protect long term flooding if your exit paths are not wide/deep enough... the ones in these pics are good enough to give me time to open the regular drain gates (shown further down). If you want full protection, aim for 1.5-2x the area of your intake valve
Run into a sudden flood anyway? Instead of just opening the drain, set the lever to be repeat pulled... it'll destroy the water as well. Just remember to clear any debris that may have been pushed onto them before attempting to close for refill.
This construction can be completed with a single miner... but I'd recommend 2-3 to get it done right quickly... with 3-4 miners, a mechanic, cutter, and carpenter (wood floodgates) it's normally possible to get this built before the first thaw of your fort was embarked in winter.
If you're in a warm-hot area that doesn't get enough freeze time to channel the riverbed, channel 2 squares off the side of the river, floor over the channel drops, then channel the area between your new outdoor floors and the water source. This'll pull water off the side - it's not quite as fast but it works all the same.
If you forget to build the bridge over the top before starting to fill the cistern, you're likely to wash (read "kill") a dwarf or 4 into the pit ... in fact, lets just designate the riverbed (below the bridge and the water areas around that) as restricted (d-o-r) and the bridge as high traffic (d-o-h).
Again, the layers are the important part - the actual size of the cistern and fill areas to other wells are entirely up to you. Some people like to have separate cisterns for opposite sides of their base, and most people that survive past the cavern layers will find a need for an additional cistern deep in the earth. The same concept applies: you want access for cleaning, overflow, intake, and drain... having a fill layer between allows more water available at once meaning more time between refills.
As the pictures here may be too small for (most) people to read, it appears that photobucket has decided to shrink my pictures (which is actually good for the forum, just not readability). Try zooming in ([Ctrl]+[+]). I'll fix it later so don't bother commenting on the "I can't read blah blah blah." Please feel free to comment here, inquire, suggest, share, copy, re-post, link to, or otherwise put this information anywhere you want... just remember to mention me somewhere, preferably linking back to this.