Waterbeds were one of those novel ideas of the 70's that (mostly) did us all a favor and stayed in the 70's .....
They have mad weight issues (full, a king-size one can weigh almost a ton), are easily destroyed (and quite destructive when it happens - water everywhere), and aren't even seriously good at being, like actual beds. They can be heated easily, but let's be real here, electric blankets exist...
Thankfully, I have not tried having sex in one, and having only a few (memorably bad) experiences sleeping in them, I don't think I ever will.
Tempurpedic memory foam beds are also lousy for sex.
...and just about everything else. Call me crazy, but I still like a really nicely made coil-spring mattress more than any of the other modern ideas out there. I haven't tried purple/casper and their silly structural plastic construction idea, but I'm not super excited to.
That being said, the highly overpriced air mattress from sleep number is, surprisingly, not a horrible thing. It's not the BEST thing I've ever slept on, but it's adjustable, which is nice, and almost as good as a really good coil spring (and thus, far better than a cheap coil spring).