I live outside Atlanta, arguably in the heart of the sports-nut south, and basically the birthplace of youth baseball insanity. I've had three kids go through youth sports, but not tackle football.
In my experience, again excluding football, there are plenty of options for the level of intensity you want for your sports. We started our kids in the mega-church leagues, and played basketball, soccer, softball and baseball there. That's your "just for fun" experience. For my daughters, and for my son for a while, that was adequate. That's where the parents are all nice, the coaches kiss the kids asses, and if your nine year old misses a grounder because he's playing with dandylions, the coach is going to give him a hug and probably the game ball for his team spirit.
As kids get older, for some of them that's enough. It was for my daughters, they never wanted more. At 5-6 my daughter was EXCELLENT at soccer, well advanced beyond her age peers. But she only liked it, not loved it. She had travel soccer written all over her at that age, but she just wanted to play one season a year, and with the same church team where she knew the girls. And each year she fell further back to the pack, as the other girls were playing year round, going to camps, and just generally were more interested. I never pushed her to move on, just let her play out the string. It was a little disappointing to me only in that there are other kids with nowhere near the natural talent she had that would LOVE to be able to play at a high level.
Like my son. My 10-year old son is a C+/B- talent at all his sports, but he's serious about them and mentally dialed in. He cares. He probably isn't good enough to even play high school sports (he doesn't know that yet), but sports are important to them. For him, we were able to move to the town league, which is a step more competitive. He wanted that. I told him that if he switched, he'd go from being the 2nd or 3rd best player on his team to the 2nd or 3rd worst probably, but he wanted it, so we did it. The coaches don't yell and scream or swear, but they do expect/demand a certain level of attention and effort. When they tell a kid to do something and the kid just doesn't do it, they will let the kid know. I don't have a problem with that for a 9 or 10 year old, that's the way we treat him, I expect teachers to treat him, and there's no reason why a coach shouldn't be the same way.
That will be a high enough level for him skill wise. But if he was too good or competitive for this league, then their are travel leagues that are more competitive, and I would expect to come with more intense coaches that yell more and demand more. By the time a kid is 9, 10, 11 some kids are ready for that. That setup holds for baseball, softball, soccer, basketball, etc. around here. My son also plays church league for basketball, and an independent flag football league. That's what works for him, his talent level and commitment, as well as our family's desired commitment, for each sport.
I'll tell you, seeing overbearing coaches are bad, but it's also no fun to see your 10 year old out there trying to learn and do well, and there's other kids disrupting practice, dicking around, talking over the coach, and making no effort, and then going over between innings to sit on Mommy's lap and get his hair stroked.
It seems like the solution is pretty straightforward. Eliminate the worst 3% of parents and coaches that are just lunatics and unfit to be involved with youth sports in any way. Then have different levels available, and have your kid in the appropriate level for their maturity, interest and ability. That's it. We've been fortunate enough to be able to follow that model, and the youth sports experience has been 100% positive.
I realize that is over-idealistic and can never really be done universally. I'm fortunate to live in a populous area with a lot of options, I imagine in smaller towns there is only one game in town. And football is a beast of it's own, where even in an area like this the sheer size of a youth football operation means there's only one or two options.
