Benefits to Abstracting SQL Tables

Question

I'm going to use Drupal as my example, but it extends to other situations as well.

I've seen database schema that are abstracted away from what a DBA would implement, most notably with Drupal. For example, When you create a Content Type in Drupal (equivalent of table), it abstracts away the fields, as new tables, in the form of field_{machineName}, which then relates back to the original "parent" table (node_type in drupal).

When I'm dealing with MVC frameworks, like Rails, Django, or Laravel, we don't abstract away the tables, so fields are stored right on the table itself, not related back.

What benefits do you get from implementing an abstracted table rather than a concrete table? Are there situations that this should be used, or is it generally a bad idea? It seems like a bad design choice to me, but I'm a fairly isolated programmer.

A feeble attempt to illustrate my question, using a "Book" example.

EDIT

I see that my diagram isn't exactly accurate. I will post a new one that reflects that node_id should relate to a node table, which then stores a reference to node_type

Answer 1

My 2 cents:

Pros of abstraction :

• Can handle any entity type the same exact way.
• you can define "Generic UI" & plugin system based on node type
• You can define Generic behaviours (like ACL based on node field title) applicable to any model built.

Cons of abstraction:

• You cannot see the "final" model directly (however, you may rebuild an image of it)
• performance & querying complexity (can be mitigated with "flat" indexation tables)

So i would say :

• for "open datamodel" , able to suit any need of data representation , abstraction has many advantages (at the cost of readability & performance). That's the typical case of many "multipurpose meta builders" (like Drupal)

• If you know what you are modelizing and are defining an "application" rather than an "application factory" , you'd better use a "specific" datamodel for the application scope.

Another "meta" database construction pattern i like to use is :

• Defining entity specific tables with associated "generic" table. (typed base table & open "key/value" property table associated with each entry of the base entity table). So it gives the ability to add "extra info" to existing base entity without having to modify the core model at each iteration. Letting the choice to find out what "properties" to migrate in the base table over time.

Another variant of this is EAV model , used for example in Magento.

Answer 2

IMHO, here are the 2 main reasons why the Drupal schema is build this way

• Fields are dynamic, they cab be added and removed from a an entity bundle at any time fron the Web UI. Using separated table ease mutation of the schema.
• Field values can be translated, in Drupal 7 the translations is a done at the field level. The title field could be translatable, while the content field may be not.

Note that most of the times, when using the Drupal APIs, you don't have to deal with these tables.