It depends on a lot of things, notably among them how many spokes there are.
But one important thing is that you never want the spokes to go slack, and that the majority of the change in spoke tension is on the spokes that are more or less directly under the hub - Jobst Brandt points this out in his book "The Bicycle Wheel" and summarizes it by saying 'the wheel stands on its bottom spokes' - their tension decreases and the other ones' tension stays mostly the same, which is how force on the hub is transmitted downward through the rim. You can't push on a rope, or a spoke, but you can 'push' the front of someone playing tug of war by pushing on the back of their opponent - that's how this works.
So, anyway, working backward, you can estimate the amount of force you need on (say) 4 spokes to keep them from never going slack under the biggest load you'll put on the wheel - which is going to be several multiples of the static amount because you have to account for bumps.
Since you can only put so much tension on any one spoke (the nipples are brass or aluminum and will strip, and the rim can take only so much pressure around the eyelet, and the hub flange has its limits too) heavier duty conventional wheels typically have more spokes. For symmetrically spoked wheels I think people wind up with around 100kgf per spoke - so as a person who with bike and gear is about 100kg, I usually choose 36 spoke wheels, so between front and back I'm mainly messing with 8 spokes at a time, and a good safety factor for momentary jolts.
There are lots of variables at play, but this may explain why heavier duty wheels have more spokes. There are a lot of other things that need to be balanced, such as consequences of breaking a spoke (not as much if you have more of them), weight, aerodynamic effects, cashola, etc.
To answer your exact question, I think typically the type of riding does NOT govern the spoke tension itself -- generally you figure out how much the wheel is going to need to support and you select a design with the number of spokes and crossing pattern that is suitable to the application.
There are holy wars about how tight exactly to make a wheel; myself I try to make my wheels as tight as I can without making them want to go into a taco shape.
I'm undoubtedly going to be subject to correction on details, because, hey, wheel building is holy war territory. I'm not a pro, but I have built five wheels, ridden them a bunch, and they haven't given me any troubles.