I use a dual tuner (Hauppauge PVR500MCE) in the following configuration:
(1) Direct Cable (from wall) via coax
(2) Digital cable STB via S-Video
This allows me to record and watch at the same time, including recording a movie in the premium digital channels, while I'm watching the hockey game on TSN.
In addition, I have an ATSC tuner for pulling in OTA HDTV. You need significant storage to record -- up to 20GB/hour.
If you're doing any hi-def stuff, don't scrimp on the CPU and GPU. I'm currently testing a Intel 930 Dual Core CPU -- very nice.