Hi JDMWDC,
The support core of a mattress is the support layer at the bottom of the mattress (or in two sided mattresses in the middle). This is what provides the primary support for the upper layers which are either comfort layers or comfort layers and transition layers (in etween the comfort layers and the support core). Memory foam can come in more or less “supportive” or firmer versions relative to other types of memory foam versions and is certainly suitable for use in the middle transition layers (which some manufacturers call support layers rather than a support core) but they are all too soft (and get softer with heat, humidity, and constant pressure) to be used as the primary support core of a mattress. Your heavier parts would sink down much too far over the course of the night and put you out of alignment.
This is why you will always see a polyfoam, latex, or innerspring deep support layer (or other type of firm support core) under the memory foam in a mattress.
Terminology can be confusing in an industry where words have multiple meanings … and not all of them are correct 
There is nothing on this site that speaks of an “ideal” except in relative terms depending on the qualities that someone is looking for or suggestions to exclude certain low quality materials from a mattress. For example the memory foam guidelines are here and suggests avoiding memory foam layers that are lower than 4 lbs density.
One of the links in the “read me first post” that is after my signature is post #2 here which is as close as anyone could come in defining an “ideal” mattress. Each person’s ideal purchase will also depend on all the most important parts of what I call their personal value equation (also linked in the “read me first” post)
The most effective way I know to buy an “ideal” mattress is to follow the steps in the “read me first” post one at a time which will give you all the basic information, steps, and guidelines you will need to make the best possible choices and perhaps most importantly avoid the lower quality/value choices such as the Sealy memoryworks or any mattress or manufacturer which doesn’t disclose the quality of the materials in their mattresses which is the only way to make meaningful quality and value comparisons between mattresses.
So you have certainly “missed something” or have misread (or perhaps not read) some of the other information on the site but that’s what the forum is for so you can ask your questions and be “armed” with the more factual and meaningful information you need to make the best choices
Phoenix