I will to try to give a few numbers you may find useful based on what I have found over my years of connecting one thing together or the other.
On Tapcons, Philcons or Con-certs or just good old concrete screws, 1" embedment into is the typical depth I see referred to. The screws are sold at those lengths with that in mind. 1 3/4" for 1X material. I checked the website and it is mentioned that the depth can be as deep as 1 3/4" bury into masonry. In my experience, in hard concrete, or brick, a screw will break after 1 1/4" into a hard surface. If you are setting it in the joints of the brick or perhaps cinder block or a soft brick like older handmade brick that are not real dark, 1 3/4" into the material is pretty easy to do. There are 2 different diameters to chose from too. That can be as simple as the judgment call of "this thing is really heavy and I don't want it to move so I will use the bigger diameter one" or, "This will not be too tough to set, so the small one will do." If the item you are going to secure is going to vibrate, I strongly advise against using concrete screws, unless glue can be used in some way to help keep it together. Wedge fasteners may be a better choice for that, or better still an anchor bolt with expansive concrete set into a drilled, cleaned hole will hold the most reliable if strength and resistance to vibration is what you are looking for.
When fastening wood, once the piece to be fastened is gone through, generally I find 1" into the second, or the piece you are fastening to is enough to hold. Of course, there are variations on this, depending on the circumstances. On deck boards, IMHO, 3" screws for a 5/4 deck board is way over kill, 2 1/2" is plenty for that. For 2X deck boards, 3" is a good fit. I have also seen 3/4" or less, work well in thin material that you may not want to go all the way through, but there may be need for many screws needed in a given area to have a strong hold. When screwing into oak or other hardwoods, 3/4" will hold very well. When fastening a piece to the end of another, as in making a corner for a box, I heard a long time ago the screws needed to be 3 times longer than the piece you are fastening, because end grain does not hold a screw as well as going into the wood perpendicular to the grain. The part about end grain not holding a screw well is true, but I find the screw does not need to be that long at all. A 4 1/2" screw to hold 2X together?? A bury of 2" works pretty good in most cases, in a joint that does not have much stress applied to it. If the screw can be slightly angled, instead of going straight into the end grain, what it can hold will be greatly increased. Whenever possible/practical, it is best in any case to use pilot holes in the piece to be fastened, to reduce or eliminate splitting. There will be cases where the pilot needs to be in the other piece as well to prevent splitting. Oak is a good example of needing properly sized pilot holes in each piece. I might add more to this when I get more time, or depending on the comments received, if clarification is needed.
Western Woods Products Association and West Coast Lumbermen’s Association have produced tables and charts based on “stress” for wood fasteners in shear and withdrawal in various species of wood.
The values are based on the diameter of the fastener and if it is in “stress” parallel to grain or perpendicular to grain.
So, for wood to wood connections (the spacing is closer for metal side plates) it is:
For PARALLEL to grain loading: 1) center to center along the grain is 4 Times it’s diameter, 2) row spacing is 3 Times across the grain (parallel), 3) edge distance is 1.5 Times , and 4) end distance (in tension) is 7 Times diameter and when in compression it can be 4 Times diameter...and when installed with less, it’s certain to split the wood.
For PERPENDICULAR to grain: 1) center to center across grain is 5 Times diameter for fasteners with a slenderness ratio of 2 and 6 Times diameter for fasteners with a slenderness ratios between 2 and 5, 2) row spacing is 4 Times diameter, and 3) edge distance is 4 Times for stressed side and is unimportant for opposite side.
There’s a formula for calculating STAGGERED spacing (and they can be closer together when staggered) but it’s too hard to calculate, so just know that they can be closer if staggered.
So, to answer your question, yes it does matter and there’s a way to determine the value if you’re Einstein.
Best Answer
I don't know if there's something for screws in wood, as wood's a rather strange material.
For bolts into steel, however, there's the AISC Manual (no prices listed, but expect it to be in the $300+ range); there used to be a separate book on joints, so you could calculate the strength based on the size of the fastener, bolt pattern, etc. There was also a section for calculating the strength of different weld patterns.
Now, the rules for the optimal strengthy are going to be similar -- further apart will support a greater moment, so when the contact patch is a square, you want the first two to be in opposing corners. If using four, fill all of the corners. However, because it's wood, you have a chance of spliting, so you don't want to go too close to the edges. (if you have to; pre-drill).
The other thing to remember is that with screws and bolts, the fasteners shouldn't be taking the full load -- they're pulling the structural material together, so that the load's transfered as friction ... this means if you see a gap between the two pieces, you need more fasteners. If you have a really large contact patch, drop another screw in the middle.