What this is about, creating your own projects, applications or games and there's a lot of great homebrew stuff around. Getting set up for this does allow you to play pirated games though which isn't so great for the industry and a reason for bad press on the home brew scene.
Cyclo DS is my choice for cartridge which I got from Abotcity. The reason, this is updated on a regular basis and it auto patches, which means you don't have to mess around with application roms, stick them on a micro SD card and away they go, it is also nicely made and can handle larger than 2GB cards. I use a 8GB card that I got from Newegg.
Apart from being updated regulary, what makes this the best card to use, you don't have to stick your roms in the root of the card, you can stick them in folders so you can organise your apps how you like and stick photos and media files on there and play them with a media player.
Okay, your set up, now what, a couple sites to start you off are NDS Homebrew and NDS Hive. You'll find applications ranging from audio tools and painting tools to maps, weather tools and other apps that use your wifi connection as well as home made games.