I use something from El Gato. It's a USB-based HDTV tuner for ATSC and NTSC, including ClearQAM encrypted cable. Works fine on my G4 Mac Mini and uses TitanTV and TV Guide for EPG (guide) info. It can scan my Comcast analog/digital channels (HD too) and my OTA channels. Works great and automatically converts to MP4 and other formats and can also automatically add shows to iTunes in the background. I got it at Macworld for about $120 or so.
There's a PC version from a company called Hauppage (SP?). If you want to go all out, I believe there's a handful of Cablecard ready PCs that you could use w/ MediaCenter. You might want to check out the HDTV podcast on iTunes. They've reviewed many products like this in the past.
If you get a Mac Mini or similar sized-PC, you can use that as a Media Room PC. The newer Mac Minis can also go from mini-display port to HDMI. I'm using the DVI to my plasma. Then you can run Netflix and Hulu on either of those machines. You can also try Boxee or Plex if you have a Mac.
