One thing to note on winning and failure is scale of either.
Some DMs have, for sake of ensuring high level specialized PCs can lose, made the setting rather unrealistic/immersion breaking.
For instance, if a PC tries to do something that's done every single day by countless level 1-3 commoners, and fails at it despite being a specialist - one has to wonder, how do commoners ever pass that DC?
For instance, DC 30/40 to not slip in mud while in rain. DC 50 lore astronomy to navigate to Waterdeep from Baldur's Gate (now, if it was simply.. DC 15 to navigate there, and every 5 points above 15 means the journey is faster - that makes sense to put a DC 50 of "finding a route that's X fast". However, that's not what they do. If you fail the DC of 50, you'll wind up somehow sailing south somehow, even if your roll surpassed 30).
This kind of approach to DCs I always found awfully immersion breaking, as I kept asking "How the hell can people under level 10 ever hope to pass these DCS?!". It got so bad at one time, I went and wrote up a suggestion hoping DMs would heed it for determining how difficult an action should be. Copy pasted from before:
You have a concept.
You read d20SRB/PhB/DMG for details to figure out how to make a build that justifies the concept. This includes getting skill ranks within a certain range of DC to guarantee or make success more likely.
You concede that the DCs are "Ideal situations", and refer to diplomacy/Scrying/teleport rolls to figure out how un-ideal situations may affect the skill rolls. You plan your build around non-ideal situations that make sense.
You decide what makes sense by asking the following questions:
Would a level 0 commoner be able to do this?
If not, would a level 4 commoner be able to do this? (adjust for 8 so it's in line with BG having adventurers at level 15 x 2)
If not, would a level 0 Expert/Adept/Warrior/Aristocrat be able to do this?
If not, would a level 4 Expert/Adept/Warrior/Aristocrat be able to do this? (adjust for 8)
If not, would a level 1 Adventurer classed character be able to do this? (adjust to 2)
If not, would a level 4 adventurer classed character be able to do this? (adjust to 8)
If not, would a level 8 adventurer classed character be able to do this? (adjust to 16)
If not, would a level 15 adventurer classed character be able to do this? (Adjust to 30)
If not, would a level 20 adventurer classed character be able to do this? (Adjust to 40)
Make the following assumptions to answer the above:
The world is inhabited primarily by level 0 commoners who do things just fine
Level 4 commoners tend to be veterans at their professions
Level 0 specialists tend to be the second most common, and represent the majority of craftsmen/skilled labourers/scholars
Level 4 Specialists tend to be guild masters, senior scholars, high ranking millitary officers, what have you
A level 1 adventurer is relatively common, but tend to be able to accomplish more than most people.
A level 4 adventurer is still relatively common, but are rarely hindered by mundane aspects of life. Instead, they are able to survive sub-optimal circumnstances (think Army Rangers/Delta Force being trained to go days with barely no sleep and still do their job)
A level 8 adventurer is prepared to deal with extraoridinary matters. The only thing that will throw them off is doing things in the heat of combat, having no equipment AND bad environmental conditions. These guys are elites.
A level 15 adventurer does things that are the stuff of legends. A level 15 adventurer will somehow be able to find a hidden uncharted island working off of legends and hearsay and minimal data provided by poor weather conditions, broken instruments and such. They will not succeed always, but they have a good chance to do so. Doing a practised thing in poor conditions does not affect their chance of success.
A level 20 adventurer does what a level 15 does, except with much higher certainity. It also tends into mythical qualities, the stuff that greeks tell of their greatest heroes.
For the above, use the following rules depending on intended safety margin:
If NOT using the assumption that the world is twice its power level/PCs are half their own:
For a level 0 Commoner, DC of 10 is the highest due to being untrained. Anything above DC 10 is very unlikely, unless training is not required for the skill or it's a safe. However, the idea remains that DC of 5 is realistic, 10 is possible, 15 is unlikely, 20 is lucky.
For a level 0 specialist, adjust all DCs by 2-6 depending on stat spread. I'd go with 5 (full investment and ability affinity)
For a level 4 commoner, DCs may be higher by 2-3 (assume cross-class/low int/1 ability affinity)
For a level 4 specialist, Commoner 0 DCs may be increased by 5-9 (depending on stat spread, assuming class skill).
For a level 1-4 adventurer, use specialist values, but tend towards higher due to stat affinity
For a level 8 adventurer, take commoner 0 values and add 11 and stat affinity of 3 (+14)
For level 15, take commoner 0 values and add 18 and stat affinity of 4 (+22)
For level 20, take commoner 0 and add 23 and stat affinity of 5 (+28)
IF using the 2X assumption.
For a level 0 commoner, values are the same. 5 for easy, 10 for possible, 15 for unlikely, 20 for lucky
For level 0 specialist, values are the same (add 2-6 to commoner 0)
For level 4 commoner (8), take commoner 0 and add 5 (cross class) and affinity of 1 (+6)
For level 4 specialist (8), take commoner 0 and add 11 (class) and affinity of 2 (+13)
For adventurer 1-4 (2-8), do as level 4 specialist, but affinity +1 compared to specialist for apporpriate. (+14 at 4)
For adventurer 8 (16), take commoner 0 and add 19 to the DC for class skill and affinity of 4 (+25)
For adventurer of 15 (30), take commoner 0 and add 33 and affinity of 6 (39)
For adventurer of 20 (40), take commoner 0 and add 43 and affinity of 8 (51)
As long as the DMs keep in mind the probability of an NPC needing to do the same task, and what kind of NPC succeeding at it, DCs will remain normal.
It's an intuitive system that doesn't need looking at d20srd if the complaint is "it crashed the DM client to alt tab".
A challenge that puts one encounters every day would be aimed at a commoner 0.
A challenge that puts an average working NPC to the test would be aimed at specialist 0.
A challenge that puts an experienced commoner to the test would be at commoner 4
A challenge that requires a talented specialist with decades of experience would be at specialist 4
A Challenge that puts Theseus/Odysseus to the test would be aimed at Adventurer 8
A challenge that puts Orion to the test would be aimed at Adventurer 15.
A challenge that puts Achilles/Heracles to the test would be aimed at adventurer 20.
Now think about the ridiculous DCs some DMs give (fit for Achilles/Heracles) for tasks that random NPCs do day to day (which they succeed at, otherwise society would fall apart).
Codify the above so that everyone can look at it and then assign their skill points/saves according to what they want their character to accomplish. Most will safely be able to aim at the "Army Ranger/Delta Force" level of specialist/adventurer 4 (8) and safely justify their RP.
Those who want herculean achievements (say, swaying an entire city with a moving speech) will have to pass a ridiculous DC - but such is acceptable. Furthermore, hidden modifiers can still exists as long as they make sense and are communicated somehow (especially for diplomacy, where the original opinion heavily influences the result. The same roll that makes a hostile person indifferent will make the indifferent person ready to die for your ideals.)
Essentially, for a challenge/loss to not be immersion breaking, the one that makes the DC to determine failure has to ask a few question:
"How often is this deed the PC attempted done? Who are the people who tend to do deeds like this? Is the challenge fuzzy (i.e: there's tiers of success/failure, like "You pulled off the challenge as a commoner would, but this costs extra resources/time. Were you better/luckier, you'd have done it faster/more efficiently"), or clear cut (You either balance the slippery rope or not)
As for what is conductive to a good story or not - I maintain this focus on "storytelling" in itself hinders immersion. We should focus on our characters, ensure they live rounded lives with rounded desires, and let their experiences/emotions be the prime focus. Stories will emerge from that, and their quality is irrelevant so long the experience is enjoyable.
For me, failure is fine so long it does not trade the ability to experience things as a given character for "drama/story." Or that it does not hijack roleplay, and focus it wholly on itself. (For instance - the Black Orc wars completely nullified any kind of RP elven players wanted to have, for all RP had to be done dealing with the threat, leaving no space for interpersonal roleplay or just less stressful matters. Likewise with a more recent event where a PC a lot of other PCs cared for got bedridden with an unknown illness for weeks - it meant breaking character to do anything but care for her, or research her illness. And again, we had a kidnapping years ago that left a PC mentally broken - I wound up taking an OOC break at that time as I could not justify my own character doing anything but doting on that friend of hers).
One way I'd enjoy failure is if it affected (guild) resources and influence. Say, there's an event to escort a group somewhere. PC deaths during the event mean that journey takes longer, as the wounded need to recover and healing spells must be spent. This loss of time would mean either failing the escort mission, or increasing costs, or diminishing its influence/impact on plot. Failure still holds weight/consequence, but that consequence does not hijack the participants' ability to experience the world as their player characters.
But then, I play RPGs neither to win, or for stories. I just play them as a way to experience life in a world that's not our own, through the eye of someone different from me. Anything else is just a happy coincidence.
But that's a matter of preference, and no one should be shamed for liking either. Everyone, IMO, should work to ensure others don't have to deal with stuff in RP that they are OOCly uncomfortable with.
