Actually, I believe the most realistic puzzle is to not use a puzzle at all. If I was a super-genius mage, I'd make damn sure to use a mechanism that no one will ever be able to figure out or circumvent. Putting clues somewhere, no matter how cryptic, is _stupid_ if I don't want anyone to figure it out.
Exactly. Unless the mage in question is particularly forgetful, the sequence is just going to be a random combination which he'll then remember.
Actually, I recall one of the Exalted pre-gen adventures was built on this principle - the dungeon was filled with traps, secret doors, and puzzles... but all the clues were wrong because the in-game designers had recognised the silliness of giving out clues for people.
(That said, in my experience most IT guys seem to have a couple of "password" stories - stories of otherwise-intelligent people who put their password on a post-it note on their PC, or set the password to "password", or both. So perhaps it's not all that unrealistic...)
Likewise I never understood why magic items aren't all custom-made to only work for the intended recipient (and naturally its creator).
So why would there ever be puzzles or magic items that anyone could use?
Because it wouldn't be fun otherwise!
That's exactly it. Puzzles aren't realistic, but they are (or at least can be) fun - they're there to challenge the
players, not the characters. (And, besides, the 'realism' argument is a bit weak when you stick it up against
everything else in the game.)
Anyway, regarding the actual difficulty of puzzles, I recommend a model I observed and then stole from the Lego Star Wars games:
In Lego Star Wars, each level has three rings of puzzles.
In order to complete the level, there tend to be one or two small and easy puzzles to solve. A door needs opened, so you have to figure out how... but that's not hard. Or you need to use the Force to move the plug that's sitting
right there into a socket that is also sitting right there. Easy, simple, and dealt with quickly.
However, there are then various optional sub-quests you can complete within the level, if you are so inclined. Basically, the levels each have ten 'canisters' hidden throughout - you don't need these to proceed, but if you are so inclined you can spend more time digging these out. (In LSW, you tend not to be able to collect all of these in a standard walk-through - you have to revisit the level later in 'free play mode'.)
And then, finally, there are a number of Easter Eggs hidden in the game, so that people who are
really obsessed can spend ages just exploring, visit every little nook, and there are things that are placed there just for them, so they can say "cool!".
Applying that to D&D, then, I would suggest the following:
To complete the quest(s) that were the party's motivation for going to the dungeon in the first place, there should be a small number of fairly easy puzzles. These may well not be true puzzles as such at all - if there are multiple routes through the dungeon that go to different places, merely finding the right one may be the puzzle. Either way, keep these puzzles few in number and easily solved.
In addition, the dungeon could (and probably should) be laced with a number of other puzzles that are
not required to solve the dungeon, but give access to additional treasure, or allow them to swing an NPC to their side, or whatever. And so, players who are interested can spend a bit more time dealing with these puzzles, but players who aren't interested or can't solve them can just move on without losing out too badly. Being optional, these puzzles can be significantly harder; there should probably be a range, though, from "quite challenging" to "fiendish".
And then, finally, there could perhaps be a few
other puzzles that serve no other purpose than to just have players say "cool!" Like when they realise that the dragon that was referenced in their very first adventure is actually the same dragon whose bones are used in the giant's throne this time. Or something.
(Incidentally, to the OP: In general I would caution against tying a trap to a puzzle. It's frustrating enough to get the wrong answer; it is considerably worse to get the wrong answer
and get zapped for it. At worst, I would suggest a "three strikes, then lockdown" approach to the puzzle.)