There are a ton of different options, and it all just depends on what kind of money you are willing to put down, and what systems you will be using.
I myself use an XRGB Mini, and an RGB modded Duo-RX.
The XRGB is extremely good, but it comes at a high cost. On the flip-side, it will work with any console that outputs RGB. I think it's the best option for people who want to use and HD TV, and do a lot of retro gaming.
The Duo R works is effectively exactly the same, and is cheaper. There are also now no-mod cables that work with the ext port on PC Engine or Turbografx-16 machines. I haven't used them, but they seem to output good quality RGB. The downside is no CD games.
If you are just wanting to plug the system into some sort of HDMI adapter, it kind of depends on the tv you use, and what kind of upscaling capability it has built in. One thing is for sure. It will not be as pretty. If you use normal composite cables, even if you put them into an HDMI adapter, it will still be composite video.
Another option is to run an emulator on your PC and play them through your tv. I don't like it personally, but it's the cheapest way and I'm sure there are plenty of people on these boards who do that.