Great answer from Aram! Which includes an explanation of how "custom fields" are technically implemented in a Drupal database.
If you wonder where the "schema" (as in the question) originates from, I suggest a higher level of abstraction, which I (still) call the "data model" of which the Drupal database is a technical implementation. And that automatically leads to "Database normalization", a concept introduced by E. Codd. The most important concepts I remember from the time I graduated are these:
I do remember there were even more (like 4NF, etc.).
But the "Rule" we always used to measure the quality of our data model was this:
"Everything depends on the key, nothing but the key, so help me ... Codd" ...
About the actual "creation" of such schemas (diagrams), eg to model many-to-many relationships: back in the 90s (the time of Windows 3.1 and OS/2) there was IBM's "AD Cycle". Part of the "hype" then was to use "CASE tools".
One of them was "Bachman", an "expert system" created/inspired by Mr. "Charles W. (Charlie) Bachman", who was heavily involved in the creation of IDMS (a DBMS used in mainframes ... still today!). That's also where the "Bachman diagrams" originate from (which are still in use today ...).
We used the Bachman tool to:
- automagically validate our data model (the wait messages was something like "wait while the bow tie expert advisor is verifying your design" ...) and
- automagically forward engineer such datamodel to DBMS implementations for IDMS or DB2 and
- reverse engineer an existing database implementation (eg hierarchical IMS) to a data model.
It would be fun (interesting) to do some similar reverse engineering today with the Drupal database schema, to see how "Bachman" would evaluate its corresponding datamodel ... and discover possible violations of 1NF, 2NF or 3NF ... (if any).
Note: More interesting background info can be found in Add foreign keys to core, as suggested in @mpdonadio's comment below.
PS: I bet Dries, while he was graduating, must also have heard of Codd ... or "Charlie".