The SPECIAL stats directly affect your SKILLS at the start of the game, click on a specific skill, and right below the name of the skill - it says exactly what determins it - for example lockpick I believe is AG times something, plus something, equals: something <---your starting skill.
You don't have to understand exactly how it works, but by quickly checking what skill is governed by what SPECIAL stat, you can get more of an idea of what stats you want. If you plan to do a lot of fist-bashing, put more points into STrength, if you plan to talk and be sciency, put more into CHarisma and INtelligence - and so on.
Now, have in mind that this immediate and direct effect only affects how your skills start out
Other effects are more subtle, for example INtelligence determines how many skill-points you get each time you level up.
AGility determines how many Action Points you get during combat
STrength determines how much carry weight
ENdurance how much HP you get, PErception has an effect in long-distance shooting, not sure exactly how, but it is very noticeable in Fallout Tactics, where you can compare stats side by side.
A very few quests will check your main stats directly, as in, they'll require an exact ammount for you to pass in an ideal manner. You can always pass these quests, but in less than ideal manner, I guess (For example - use drugs to temporarily increase these stats)
In the end, you guess correctly (including with IN. coming in handy in Vault City), you will have to balance - but don't despair, notice what I said above - there's always a way around, even if it's not ideal. You will have to settle on a "favorite" build, make a character that has strengths and weaknesses, you simply cannot escape this. This game is playable, beatable, and even pwnable with any character build, be it a fighter, thinker or sneaker. You can always use in-game drugs (click on them to see their effects, there's one for every stat except Luck)