I second the Select Comfort Sleep Number beds. My wife and I have entirely different needs when it comes to sleeping, and it was the only bed we could both agree on. after visiting numerous mattress stores.
Select Comfort has numerous models, but the air chambers themselves are the same for each model. The difference is what the air chambers are encased in. Anything from a simple pad to deluxe pillow top (and they also have those adjustable reclining models).
Rather than going to a regular mattress store where they usually only have the single show model, try to go to one of the Select Comfort retail stores so you can actually try the different types of padding they have. We ended up choosing the mid-range model after trying them all out.
The beds come with a plastic box frame (like a box spring but hollow) for mattress support. A waterbed frame would probably be able to support the mattress without it.
As a side note, the bed has become even more practical now that my wife is pregnant with our second child (due on the 30th, expect an announcement next week

). Rather than having to buy one of those expensive body pillows to support the weight of the belly, she has let air out of the chamber to the point that her side is concave like a hammock. As a side sleeper, the curve matches the contour of her belly perfectly.