OK - will try to keep this simple:
Best Quality
Component - the video signal is split into three signals, two color and one black and white, giving you the best picture. Use component video to take advantage of the superior picture found in such signal sources such as HDTV and progressive DVD.
Component connection is three plugs - red blue and green.
Better Quality
S-Video - The video signal is split into two signals, giving you an even better quality picture. For example, text displayed on-screen using this connection is noticeably sharper than composite or coaxial (RF).
S-Video connection looks like this:
Good Quality
Composite ("yellow plug") - The video signal is carried through a single "pin". This connection type is the one that is most commonly found on video devices
Basic
Coaxial (RF) - The video and audio signals are both carried in one cable. Used for antenna and cable signals.
(The other three connection types only handle video, requiring separate connections for sound.)
In short - if your screen has component inputs, regardless of whether it's an HDTV or not - buy the component Wii cable and use it. YOU WILL NOTICE A DIFFERENCE.
If you don't have component inputs on your screen but do have an S-Video input, buy the S-Video Wii cable and use it.
If you can afford to buy the correct cables, always connect your screen to your input device using it's best inputs.