How many database tables do nodes and entities produce depending on settings

In this post I will determine how many DB tables does each option you choose make.

Lets start with NODE, make a basic node, with just title and body, when we enter data for it, it will populare 6 tables.

1. node
2. node_revision
3. node__body
4. node_revision__body
5. node_field_data
6. node_field_revision

So first 2 are for node entity and this stores some basic node info, like id, uid, lang code, 3 and 4 are for node body as we have body field.  5 and 6 are for node field data which are basically node entity properties or as they are called base fields and they are used for storing title, created, changed, uid that created node etc.  If we add new fields to node they will get 2 new tables, just like body field has. So it is important to notice that each filed will add 2 new tables as node entity is revisionable by default, you may never use revisions but drupal will act as if you do. It is also important to notice that translations do not add new tables, if you have many translations it will just be a new row in DB with different lang key.

So that is nodes, they are set to work with mose options. To have optimized DB structure lets try entities and see how they behave.

So Entities can store data in properties and fields, or we can say they can be fieldable just like nodes, they can also be bundable, translatable and revisionable. Each of this feature will bring something extra to DB structure. Let's go with simple entity, which will have just some base fields.

So if you create a new entity and add some base fields, whatever you add, even if it is translatable it will populate jos two DB tables. Lets say we created "plain_enty" as new entity, what will happen is that we will have this 2 DB tables.
1. plain_enty
2. plain_enty_field_data

First table will have just basic entity data, like id, uuid and lang_code, and if we choose that this is bundable entity there would be one more column and that is type. This is what node table has from no 1. above as it is also entity that is bundable. Second table is table that holds base fields data, so if we add 10 base fields, it will all be in that table, BaseFields don't create new DB tables, they are all stored in that plain_enty_field_data which could help in your query building as there will be less innerjoins to get data. For creating entity for storing data I would recommend using this type of settings.

If we use revisions, once they are enabled they will again double the number of DB tables used for data. So as we had above 1 and 2 now we will have 3 and 4 with suffix _revision, which will hold revision, bundle and translation data. So 4 tables in total will be max.

All this will change, if we use entities as fieldable. We can add fields to each entity, just like nodes, as much as we want, what will happen in that case is that each field will add new DB table and if it is revisionable entity then we will have 2 new tables per new field, which will in the end make many new DB fields if you have many fields.

Many people use ECK module
which by default doesn't make baseFields, but only makes new bundles with new fields which in turn make a lot of new DB tables. This is wrong in my opinion as if you are using entities as some kind of storage, you don't need fields, you can have it all done with baseFields which will make your DB less complex and your queries simpler.