for me, achievements should be something I could reasonably do on 2-3 playthroughs of the game. Most of them should be attainable in one playthrough if I try, and there should be at least one or two that are quite difficult (but not "Be in the top 10 of the multiplayer ladder" difficult).
getting collection achievements should also give you SOME benefit in the game. In Xmen Origins, for instance, even though I have the Dogtags achievement, I'd probably pick up any dogtag I saw if I started a new game, because it gives experience. Even though I have all of the riddler achievements in Batman Arkham Asylum, I'd probably get whatever riddles I could, just because it gives experience. In Assassin's Creed, I probably would NOT collect the flags after I've gotten the achievement, as they give nothing but a little mark on your completion DNA sequence. On the PS3, the flags don't even give a trophy IIRC, so there's NO reason to collect them on the PS3.
Achievements should also feel like achievements. In Oblivion, they hadn't hammered out the achievements yet, so all the achievements are "Complete all the Guilds, the Main Questline, and the Expansion Questline".. By the time Fallout 3 rolled around, the achievements were much better.
Bad achievement structure HAS sullied my enjoyment of some games. Blue Dragon for instance, you can play through the whole game and get 2 achievements out of 50.. that shouldn't happen.. Most of the achievements in Blue Dragon are "Perform this one Quicktime Minigame that you only get to do once in the whole game perfectly" and "Get to level 99".. seriously? lol