Taking this as a technical forum, I’ll indulge in some quibbling, along with trying to deal with more serious substance…
The term ‘database’ has a generic, simple, non-technical use, but in more technical contexts, it tends to refer to a relatively rich set of functionality, including searching. So, for example, rather than specifying exact details for getting a specific record, it can search based on partial names or by attributes. I note this, because the DNS does not support such functionality, which is an aspect of the DNS that is often missed. The technology of the DNS is typically referred to as doing a ‘lookup’ rather than a search, since the DNS requires an exact match to a specific name. So, it’s best thought of as doing simple key/value processing. That’s a quibble.
Another quibble is that your reference to ‘routing’ is understandable but, really, the information returned for such queries is an address, not a route. (Since I always look for an excuse to cite this paper, thanks for that excuse: https://dl.acm.org/doi/10.1145/9760.9761.) That said, the MX record creates the global routing mechanism for email. sigh.
Perhaps more substantive is the distinction between what a mechanism does vs. how it is used. The DNS has a limited, fixed set of functions. Arguably, really, it only has one: Take a specific name and return some attributes associated with it. Period. It ‘understands’ no semantics to any of this. For example, it does not distinguish between attributes containing addresses vs those containing some other kind of information.
So the distinctions occur at a higher layer and vary on the /use/ of the DNS, not on the underlying structure or function of the DNS itself. Limiting the lookup string to what is called a hostname, is a feature of such a higher-level use. The DNS, itself, does not know about that limitation. And, again, it does not know about ‘routing’ – actually ‘addressing’ – use, versus other use.
I haven’t reviewed the specification details for where hostname syntax is required, versus allowing fully general DNS node names. Those details are for a use of the DNS, not as something in the DNS itself. (As I recall, DNS documentation contains the concept of a host name, but discussion about it is, I believe, non-normative. Maybe I’ve got that wrong.) Anyhow, you are certainly correct that a node name containing an underscore means the domain name is not a host name. That is, in fact, why underscore was chosen: no chance of colliding with a hostname.
Getting to your ‘concept design’ point. Limitations such as not distinguishing among a set of TXT records under the same name are indeed a hassle. The underscore hack is a way of eliminating or at least reducing the problem. Distinctive RR records would be cleaner, of course, but the field experience with the DNS is that getting end-to-end support for new RRs has had an extremely problematic history. After some decades with this reality, people starting just working around it. Ugly, but practical.
To clarify: the underscore convention permits defining a place for semantically-constrained attributes, such as in TXT records. Such TXT records will be there for that semantic and not some other. (It’s not unreasonable to simplify all this to: The underscore naming hack separates unrelated records of the same type, such as TXT RRs.)
The reference to CNAME in DKIM is interesting. Note that RFC 6376 does not mention CNAME. While use of CNAMEs with DKIM is extremely common – and noted in an Informational RFC – it’s not in the DKIM spec itself.
A quick scan of some online documentation about this does treat things as TXT vs. CNAME. For sysadmin’s purposes, that simplification is probably reasonable. In terms of understanding DNS basics, it’s probably note.
Name resolution in the DNS is walking a sequence of node names. The CNAME construct is for that node-walking process, but makes a jump, while doing that resolution, over to another name sequence. It’s a DNS construct, not an explicit DKIM construct. (This goes back to the importance of recognizing layers of functionality.)
With all of that, I’m ultimately not understanding “But that now creates some confusion, because CNAME is designed for name resolution and (unlike TXT eg) not database lookup.”