TV tuner cards come in several varieties.
In the UK, the most useful are likely to be DVB-T tuners, for Freeview channels, and DVB-S tuners, for free-to-air satellite channels. (T for terrestrial, S for satellite, DVB for Digital Video Broadcasting.)
For both types, you'll need a TV license if you're in the UK.
If you want to watch subscription channels on your PC, you will need a separate Sky or Virgin box and a video capture card to connect to the AV output from the box (some TV tuner cards incorporate a video capture feature). It is possible to buy infra-red remote-senders and software which allow you to change the channel on the box via your computer, which is also useful if you want to schedule an automatic recording on your PC.
With a DVB-S card, you will be able to get the Freesat channels directly on your computer, but not the Freesat EPG (electronic programme guide) or channel numbering scheme, which are encrypted and only usable by Freesat branded receivers. There are ways of working around this limitation by using software which downloads EPG information off the internet.
A similar limitation exists with Freeview HD, but not Freeview in standard-definition.
If you want to watch Channel 4 HD on Freesat, you'll need a DVB-S2 capable card because the channel uses the newer DVB-S2 standard. This may also become necessary for other future channels. At present, the other channels all use plain DVB-S.
A DVB-S/DVB-S2 card will also allow you to watch non-Freesat, free-to-air channels, including European channels if your have a dish pointing at the appropriate satellite.
It goes without saying that, to receive any satellite channels, you'll need a satellite dish. If you already have a satellite receiver in the house, your dish will need a dual or quad LNB to provide enough connections for every receiver in the house. Unlike with a terrestrial aerial, you need to run a separate cable from the dish to each tuner (dual-tuner units need two cables). You can't just split the signal from one cable, because part of the tuning system is in the LNB on the dish outside and the tuners would interfere with one another.
Some cards use hardware video decoding, while others use software decoding. The advantage of a hardware decoder is that the video will run smoothly on less powerful CPUs than with software decoding. The advantage of a software decoder is that the card is cheaper.
Cards are often supplied with suitable software for watching TV. There is also free software available which should be worth a look.