Check to see what your flash cart supports first of all -- some might be SD only (original R4 being the main one people see these days), most will be SDHC (4 to 32 gigs) and there might be a few out there which support SDXC (64 gigs and up). Be careful as some shops might not even stock that low these days -- I have had several devices have this issue when someone just got a cheap memory card to get it working and 64 gigs was the lowest the shop had. I just checked my usual source for cheapo but good microSD cards and I can get a 3 pack of quite nice 32 gig cards for £17.99 so there is that.
I don't know what the complete set ended up at, or complete English + notable/playable Japanese or complete "worth playing" games for that matter. The last one is a bit variable but not likely to be too much less than complete English + notable Japanese. I have personally never felt the need to walk around with an entire library but 32 gigs should do you quite happily to play basically all the top games, whatever your chosen genres/styles are even if they don't represent the best of that style and a sizeable chunk of hidden gems and hacks to whatever. 16 will probably do fairly well for that one, 8 and 4 can be a bit harder but it is not like copying a file to a SD card is a hard trick (if you can get on wifi with your DS you can even use that, though it is slow). If you have one from an old phone or something then by all means give it a go -- if you need a bigger one then you are mostly just going to have to format it and copy the files from the smaller card across. Speed is not really a factor by the time we got to SDHC land -- some of the older SD family had some issues with some games.
If you want some sizes then http://www.advanscene.com/ will tell you what goes. Average ROM size went up as the DS went on but usually between 512Mbit (64 megabytes) and 1024Mbit (128 megabytes), with plenty much lower than that, to say nothing of ROM trimming. Not all ROM hacks will be the same size as the original, and sometimes you might want a separate game for a hack so you have the option to boot the hacked version.
Homebrew is a thing. If you are of the homebrew=emulation persuasion then can reasonably play most NES, GB, GBC, other 8 bit and kind of most megadrive games. SNES is a bit more variable but a good chunk of that too. This is not that much for a hard drive but a decent chunk of many SDHC cards. That said there are superior options for emulation that you might go for.
Back in the day the DS made a pretty decent media player (audio and video) but today most will probably skip that.
General non emulator homebrew can take a few gigs with just the games but if you decide to do all the custom WADs with doom, custom maps/audio for those games that use such things or something that can go higher. https://gbatemp.net/threads/links-to-various-gbatemp-features-over-the-years.352851/ has some nice homebrew for such things.
If it also has to manage the GBA (various GBA slot devices exist) then you also get to account for that.
I see you have asked about how much storage you need for a hacked 3DS too. And I just wanted to let you know that you don't need a DS flashcard to play DS games on a 3DS. Just use TWiLight Menu++ and you can play a ton of roms straight from your SD card on your 3DS! Check it out here!