Well there are currently 2 cards on the market. The least expensive is the Memory Card 251 which goes for about $29.99CAD. It's pretty big and should be good for about 10-15 games easily. The biggest is the Memory Card 1019 which is about $10 more than the 251 card.
I've noticed that EA games take up the most space, typically 50+ blocks so if you play a lot of their games (Need for Speed, Tiger Woods etc) step up to the 1019 card which I think is the best value. You get almost four times the storage for an extra $10 over the other card.
Keep in mind that the Nintendo branded cards tend to be more reliable. However, you can find off-brand cards with 1019 blocks for $19 or so. This probably represents the best value if willing to risk a somewhat less reliable device. If you're risk averse like me, stick with the official Nintedo Memory Card 1019.
As stated above, the 251 will suffice if you play only a few games that don't include the EA Sports stuff.
I agree, off-brand cards are bad juju. I never touch 'em cause it would be my luck to have one go bad on me just after I beat the world's toughest game.
I picked up the 1019 the week after Christmas for about $26 USD. It's a total monster. I have about 30 games, with sometimes multiple save files (profiles, etc) and I still have well over 800 units left. With what little lifespan is left in the Cube, I don't think I ever have to worry about filling it up. Of course, YMMV if you love the memory-hogging sports games.