So uh... whats more comfortable during the sex: A water bed, one of those memory foam bed things, or a standard mattress?
A relatively firm mattress. Obviously you don't want something as hard as a carpet floor but you don't want something REALLY soft or it's just too hard to maneuver around.
In a water bed, the woman sinks way down when she's on her back, which makes missionary position really hard. On all fours, your unstable and cowgirl can have a few of the same access problems as missionary. Again, it's some great fun when you're really frisky and it's good for the novelty of using the motion of the bed to do some of the work, but I wouldn't get one as a permanent bed 'cos it would just get annoying every time.
Not sure on the memory foam but providing it's not too soft it would probably work as well as any mattress. Only issue might be if its sunk in certain parts and you can't get a good, stable surface.
The other good thing about a bed is pillows. Pillows are bed sex's best friend 'cos you can use it to prop up hips for either partner, or the woman can use it for the front of her body if she's on all fours so she's not supporting her weight on her hands. They're great for other things that I won't go into detail about but I'd put them as the number 1 sex toy with any reasonable vibrator a close second.