I've been thinking about character flaws recently for games, and while I don't currently have any projects that I'm working on that require me to make flaws, it's an interesting topic.
Flaws in General
The general rule of flaws is that they should serve to aid in character spotlighting or balance.
You want a flaw that is going to make a character more interesting, not merely less powerful. The latter allows for metagaming and other exploits, especially in cases where a game doesn't cap flaws or give them an awful return on investment.
From a mechanics perspective, I think that a flaw is best seen as a mandatory element or not featured in a game; either a character should have a meaningful flaw or no flaws at all (at least in the mechanical sense; character flaws are valuable and I'll give my thoughts on that later).
Pacifism is an example: it can be a great characterization tool, or just totally kill the flow of a game.
Incompetence Flaws
These are the best in GURPS and Shadowrun. Just load your character up with total ineptitude in things they'll never do and make one of the other PCs do it.
Kidding aside, there are issues here. A good incompetence flaw has to be something that's crucial for everyone.
I love things that involve characters having awful reaction times or shoddy endurance; those make for good narrative conflict sources. However, the chance that it matters that your medieval peasant is colorblind is relatively minor, and flaws that do things like that don't necessarily add to the game, especially since it could be handled in story rather than in game mechanics.
You can further divide these into penalties and prohibitions, with the former just making things more difficult and the latter making things impossible, but the
Lightswitch Flaws
One of the things that is interesting is having flaws that alter a character temporarily or permanently by moving them away from their potentials.
The best examples I can think of are things like paladins' codes or lycanthropy's regeneration failing in the presence of silver. These are examples of flaws that restrict a character and modify them, but they're based on being a complement to power. This can be useful for balance purposes (e.g. Monks in D&D, who can't use armor or most weapons and still gain their usual bonuses), and it doesn't necessarily have to have a huge penalty.
To be honest, unless the execution is inept I can't think of any of these flaws working poorly, especially if they're paired with powers. Typically a character can still act however they want, they'll just need to figure out how to deal with the loss of power that comes with it.
The only downside is that it does need to be an exceptional ability being locked away; boring stuff or absolutely necessary stuff that is too heavily impeded by these flaws is a pain.
Vulnerability Flaws
Great in some places, iffy in others.
Basically, characters suffer more vulnerabilities based on their flaws.
Useful for narrative reasons. Mechanically it raises questions.
Can kind of hybridize with other flaws. Personally not a fan, but I like killing characters like a hybrid of George R. R. Martin and the plague, so part of that comes in the marginal difference between being specifically vulnerable to something and having regular vulnerabilities.
Where it shines is as an Achilles' Heel sort of moment, where a character is very resistant but still has one weakness.
Should You Even Use Flaws?
Flaws add a high degree of complexity relative to their return on investment. They are theoretically analogous to unique character abilities, but they're also negative rather than positive in function. While this can boost challenge (making them interesting in single-player video games, where challenge may be one of the driving player motivations), it generally causes the game to slow.
It's worth noting that challenge and difficulty are two different things. A good challenge doesn't have to be difficult: I think of the old video game Audiosurf as an example of this: the mode I played most was a simple three-lane agility test where you try to avoid bad blocks and hit good blocks. That's a challenge, not necessarily difficulty.
Difficulty involves having a skill requirement. A game like Pathfinder might have a high difficulty compared to FATE, for instance; most competitive games have low innate difficulty, with difficulty being provided by other players.
In tabletop roleplaying, your goal is to have the lowest possible difficulty in play, and flaws almost never impact difficulty. If they do, you're probably looking at a major problem, since that makes them both a detriment to characters and a pain for novice players to execute properly.
Wrapping Up
Flaws can add flavor to a game, but you need to use them well.
Consider the purpose of each flaw. Does it link to some dramatic purpose? If so, put it in. Otherwise, leave it out.