Why does Drupal store each custom field in a separated table? 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.