The real goal of support in a mattress is to keep your spine in alignment while you sleep in all your normal sleeping positions.
There are 3 main parts that contribute to spinal alignment in a mattress and they are all different but "connected" in the sense that they all contribute to alignment.
The first stops your heavier parts (like your hips/pelvis) from sinking down too far and putting your spine out of alignment (hammocking). The second "holds up" the recessed parts of your body (lumbar) so they don't "sag" because of the "space" underneath them. The third lets your shoulders/upper back sink in enough that they are also in alignment. The "challenge" is finding the best construction that will do all 3 for your unique weight distribution/shape and sleeping positions since #1 generally requires firmness under the hips, #2 requires enough softness under the hips to "fill in the space" in the lumbar, and #3 requires relative softness (compared to the hips) under the shoulders so they can sink down enough.
Side sleeping has the biggest difference between the bony parts that stick out and the recessed parts of your body so it generally needs a softer/thicker comfort (upper) layer. Back sleeping generally needs a little less. Stomach sleeping needs the thinnest firmest comfort layer (with maybe a thin soft layer for comfort) as it is very easy to sink in too far with the pelvic area in this position and hyperextend the lumbar. A pillow under the pelvis is often helpful for stomach sleepers if the mattress needs to be a little thicker/softer in the comfort layers to accomodate other sleeping positions.
As you can see these differing needs "compete" with each other. The good news though is that different materials with different qualities ... along with the layering of a mattress ... can do all 3. In more "extreme" cases, zoning (differing levels of firmness in different areas of a layer or mattress) may be necessary.
The deeper and middle layers of a mattress are the most important part of what holds up your heavier parts. The upper layers ... with help from the middle and deeper layers ... are what holds up and "pushes back on your lumbar" and supports it. The upper layers ... sometimes with some help from the middle and deeper layers (and sometimes even a tension adjustable slatted base) ... are what allows your shoulders to sink down enough.
One other factor that is important to differing degrees with different people with different sensitivities is the overall ability to sink into the upper layers enough to form a "cradle" that contours to your body shape. Without this cradle, only the bony parts that stick out will be supporting your weight and you could end up with pressure problems in your hips or shoulders or elsewhere (arms falling asleep, numbness, pain) and poor circulation while you sleep. This ability to sink "into" the upper layers for pressure relief is not the same as "sinking down" into a mattress in general to help with proper alignment.
The goal of "your perfect mattress" is to hold you up and let you sink in all the right places for your personal body makeup to keep you in alignment, and to also give you a deep enough cradle (different for different people and preferences) that your weight is "spread around" enough that you don't experience pressure problems.
A "feeling" of softness or firmness is often directly related to how well a mattress forms a cradle and distributes your weight, the depth of this cradle, how firm the deeper layers of the mattress are (to hold up your heavier parts), and how much "pushback" it has to hold up your more recessed parts (called resilience). Perception of softness and/or firmness can also be related to what is on the very top of a mattress like a quilted cover or a thicker fabric and by body weight and distribution even though this "feeling" may not contribute directly to either pressure relief or support. This is why what feels firm to one will feel soft to another.
So the "neccesities" of a mattress are:
1. Enough softness and firmness in the right places that your spine is aligned in all your sleeping positions.
2. A deep enough cradle that your weight is evenly distributed enough that you don't experience pressure issues in any of your sleeping positions.
Beyond this there are "preferences" or "options" that affect how a mattress feels to you and can also indirectly affect the necessities.
1. A deeper or shallower cradle (some people like to sleep more "in" a mattress, some like to sleep more "on" a mattress). Beyond the depth of cradle that provides enough pressure relief, how deep it goes is a personal preference as long as the spine is still aligned. People who like the feeling of softness usually prefer a deeper cradle. People who prefer a feeling of firmness usually prefer a shallower cradle.
2. Choices in quilting (the puffy part if any on top of a mattress). While this has little direct affect on the necessities, it can have a significant "indirect" effect. Some materials (even those that "feel" soft) can lessen the ability of the foam layer under it to form a cradle and relieve pressure and some can "help" to hold up your hips by stopping them from sinking into the foam as much. This layer can also have an effect on how "cool" or "hot" a mattress sleeps as it can either hinder or help with air circulation, especially if the layer under it is less breathable, tends to form a deeper cradle, or tends to "sleep hot" (like memory foam). Wool and other natural fibers and more breathable foams for example are known for their ability to breathe and "compensate" for sleeping hot.
3. Choices in fabric or "ticking". This also is partly a matter of preference and partly a matter of how it affects the foam layers in the top of your mattress (just like the quilting). More elastic and "stretcheable" materials let you sink into the foam underneath more. Tighter less stretcheable materials tend to decrease how much you sink into the foam underneath. There are also differences in ticking fabrics in terms of how breathable, temperature regulating, and of course durable they are.
4. Mattress pads. These too are available in a wide array of choices and can affect your mattress in much the same way as the quilting and ticking. Many people prefer wool or other fibers in a mattress pad rather than in the quilting because it can be removed and "aired out" as necessary. Some can also be washed. Different levels of water resistance, and breathability, as well as different "feels" also play a role in choices here.
5. Boxsprings, Slatted foundations, Tension adjustable slatted foundations. These too are available in a wide array of choices. In general ... innerspring mattresses are designed to work with a boxspring (a base with springs in it) as they help the mattress perform better. Foam mattresses need a foundation with slats that are about 2.5 - 3" inches apart or less. A center support is necessary for queen or wider mattresses and closer, wider, or thicker (less flexible) slats can also play a role in how firm a mattress feels and in some cases how far your heavier parts sink down. A tension adjustable slatted foundation can be adjusted to create firmer or softer areas under the mattress which can also help with "holding up" heavier parts or allowing the shoulders to "sink down" further to create better alignment. These are often not as firm overall as slatted foundations with thicker more rigid slats but they can help improve alignment as long as the mattress over them is not too thick. Because adjustable slatted foundations affect how far you "sink down" into a mattress and not how much you "sink into" the upper layers (form a cradle) they have little effect on pressure relief but can create a "softer" feeling.
There is one other part of your "sleeping system" that plays a critical role in both comfort and spinal alignment and that is your pillow. Even with a "perfect mattress", the wrong pillow can cause spinal alignment and pressure issues especially in the shoulder, upper back, and cervical areas. In very general terms, stomach sleeping needs a thinner softer pillow (or sometimes no pillow), back sleeping needs a medium thickness pillow which helps support the neck, and side sleeping needs a thicker pillow which helps hold up the head and keep the neck in alignment.
So ... when people say they want a "firm" mattress it can mean many different things to different people. The "best" is also as individual as people are. Mattresses that are too firm in the wrong places provide poor support. Mattresses that are too soft in the wrong places also provide poor support. In general though it would mean that the support layers (middle and bottom) of a mattress are firmer and that they prefer the minimum depth of cradle (upper comfort layers) to relieve pressure. Even people with the firmest preference though need enough softness in the top of their mattress to sink in enough to support their lumbar and relieve pressure.
Hope this helps a bit and if you need more specific suggestions ... your height/weight and general proportions and your normal sleeping positions would be helpful
Phoenix