If you don't want to watch live TV, XBMC is great. It has one of the nicest interfaces of any such system I've tried. The keyboard shortcuts take a bit of getting used to (on the original Xbox they are extremely intuitive), but for playing back files stored on harddrives it's brilliant.
If you want to watch/record live TV...
Windows Media Centre is very good, assuming you don't live in Australia where there is no EPG (without a quite complicated setup to pull data from 3rd party services).
It's interface is nice, TV cards will specify if they are compatible with it (and most are). It's included Windows Vista "Home Premium" and "Ultimate", and Windows 7 "Home Premium" and up
MediaPortal, as jerryjvl suggested, is good. The interface isn't as nice as XBMC I'd say - the Streamed MP theme is an improvement, but it's not as well integrated as the XBMC version... but, MediaPortal works, has a decent EPG, records fine, handles multiple tuners.
SageTV is another Windows based application that's worth looking at is - I tried it a few years ago and it seemed good (it costs about $80USD).
GB-PVC is free application, so is worth trying. It was one of the few I could get to work with an old obscure TV tuner I had.
MythTV is the last option I can think of - it's one of the most "powerful" systems for watching/recording TV, you can setup the "backend" separately to do the recording/transcoding, then have multiple front-ends to watch the content and schedule recordings.
The biggest problem, it can take a lot of fiddling to get setup correctly.. Distros like Mythbuntu or MythDora help simplify the setup, but getting TV cards and other hardware can still be a pain. That said, if you're building the machine from scratch, and buy recommended components it shouldn't be a problem, there's plenty of guides around - the MythTV wiki is a good place to start
Oh, LinuxMCE looks promising, and is worth a look. If you're not using it for its home-automation features, you may be best sticking to the more-mature MythTV