Since you aren't going for any records in OC, you're going to have to plan for what you are going for. A very good aircooler will just about keep up with a good 240/280mm AIO liquid cooler. A good aircooler will just about keep up with a good 120/140mm AIO liquid cooler. Neither one of those will come close to keeping up with a well thought out/planned water cooling system.
So there are factors... mild OC, medium OC, heavy OC and budget.
Mild to medium OC you can use aircoolers such as the CM Hyper212 EVO or single radiator h-55 through h-80i, thermaltake performer/pro all day long with great results. The air coolers will be slightly cheaper for the most part.
Medium to heavy OC you need to start looking for the larger aircoolers like the CM V8, Noctua NH-D14 and the twin radiators like h-100i, thermaltake extreme 2/3. The air coolers will still be slightly cheaper here too.
Heavy OC + is all Water Cooler territory with more emphasis on more components the higher the OC. You want 5GHz out of a cpu.. better be pushing some serious radiator space.
Water Cooler setups can be used on anything, even just for looks, but for the money invested, i've never heard of anyone WC a stock setup.
Can you use a twin radiator or Beast like the V8 on a mild OC? Sure, will work fantastic, even be quieter since the fans will have little or no need to spin so fast as to be obnoxiously audible.
So, 3 different types, Air, AIO, WC, just please don't confuse the All In Ones with WC, WC is open-loop, requiring periodic monitoring of liquid levels and is a plan-it/build-it yourself system, AIO's are closed-loop, install it and forget it systems.
As far as mobo's go, check the coolers specs, AIO's tend to be made to fit any mobo-socket, whereas aircoolers are a little more specific as to what mobo-sockets they will mount to. WC will fit anything, since you will buy a block to match your socket, gpu, memory, vrm, northbridge, HDD etc