Since what you're suggesting is something with plot, and what you have as favored choices are Zelda (action-adventure) and puzzle games...well, it's pretty hard to determine.

You could find some of the DS versions of Final Fantasy if you like, perhaps FFIII and FFIV. Just to promote the forum's theme.

Also, another vote for Prof. Layton and the Phoenix Wright/Apollo Justice series. The former has a plot and also loads of brain-teasing puzzles, while the latter may not be that good since it has a rather contrived plot; however, the latter also has the requisites of good sleuthing and a good ability to get to proper conclusions. If you can handle the zany antics of the game, the Ace Attorney games are good to go.

I'd risk it and go for The Dark Spire, but the game is unforgiving to those who haven't done MUDs and old-school dungeon crawlers such as Wizardry and Might & Magic. I'd recommend it if you want to get a game with little plot and a truly unforgiving difficulty, but methinks that may not be your kind of game (it's my kind of game, tho)