Correct, the MiFi acts as a wireless access point, providing a wifi signal to devices that support a wifi connection such as iPads, iPod Touches, computers/laptops, etc. It can support up to five simultaneous connections. So you can connect your computer, your phone, your iPad, a Touch, and your wife's laptop to the device all at the same time.
As to whether you'd need DSL or cable internet access, that depends on your usage. The MiFi is using 3G cellular signals, which is slower (in most cases) than DSL and much slower than cable internet. If you have a very strong signal, the speed is generally suitable for most activities, except for hi-def video. In my experience (I've used one for over three years), I think it's great for my office, where I connect a laptop and surf the web, do email, stream XM Radio online or Pandora, and sometimes watch some Slingbox or Hulu. Video is possible, but the weaker the signal, the worse buffering becomes and it can be hard to be patient.
Also, I'd assume the more devices in use simultaneously may degrade the response time as well.
But, in my opinion, the number one reason to consider whether to ditch the home DSL line is the data cap. Despite what you read on the website, the Unlimited plan is soft capped at 5GB per month, and VM reserves the right to throttle your speed back to 256k (dial up) speeds once you exceed that cap. I can easily consume 5GB in a month by playing XM Radio or Pandora 9 hours a day, five days a week. Streaming video eats up even more bandwidth.
Hope this helps.