I understand that, in order to add functionality to nodes and node types, extra fields must be created in the database.
Now, I don't see why a one-field-one-table schema is used instead of favouring the schema of just a single table for a content type, and then, in that additional table, all the extra fields. What are the adventages of that approach?
I have a site with 1,000+ tables and is being a mess to administer.
Because you can reuse the fields and attach them to more than one content type for example.
You actually get 2 tables for each field: one for storing the current version of the data (field_data_field_MACHINE_NAME), and the other one for storing the "historic" (if revisions are enabled) values (field_revision_field_MACHINE_NAME). Some content types can have enabled revisioning, meaning that shared fields need to be "flexible" enough to follow up with that, but at the same time store the values for content types with disabled revisioning.
If you had everything in one big table for content types (I think D5 did this), it would be a really big mess to manage, especially if you would share fields between entities (e.g. nodes and users).
Few suggestions:
See this answer by Clive for more information.
Inspect this on a clean install just to see how it works. Create 2 content types, add some fields (share some of them) and track what has been changed and how is the data stored in the database. You could probably dig some documentation page, but I think this is the best way to understand the schema.
Reuse the fields whenever you can in order to optimize the database more. Be aware that there might be some limitations (e.g. list options, file uploads, required or not, etc.) that will not let you customize fields for every instance, so you will have to create new ones.
I wrote this fairly quickly, but I hope I managed to explain it.
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:
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".
