Drupal: 15 years old

I've seen a thousand request for a single page when logged in as administrator (since the menu structure for the admin interface comes from the database as well).

I talk about this a lot and I must admit I rarely use words as diplomatic as naive...

Caching is not a substitute or fix for bad design.

So, let's look at the design.

Drupal wants end-users (not limited to developers) to be able to create new types of entities, add fields to those entities and store complete revision histories for those entities as they are created and edited. This should be achievable within a GUI and without needing to edit code by hand. It also wants developers to be able to distribute code that give these end users new types of fields to use.

It achieves this by creating two new tables for each additional field. One table stores just the current value of the field, and the other table stores the historical values of the field in previous revisions. An entity like a 'blog post' with three fields will therefore have a base table plus three additional tables for the current values, and a revisions table with three additional tables for the historical values. When loading an entity, these field values can be loaded via SQL joins, or (depending on the field type) via additional queries.

As this is expensive, entities can be cached, and the next attempt to load the entity will pull the complete object from memcached, Redis etc., and this is how most production sites would do it. Obviously in the case of zero caching, where you have entities with many fields (say, 30) and where you need to load many entities, you can quickly end up with many queries - hundreds or even thousands! But almost all of those go away with caching enabled.

Is this a bad design? It would certainly be possible to make the database schema line up better with the entities, which would reduce the number of queries - a single 'blog_posts' table could be queried, rather than hitting the 'node' table and then the additional field tables. But you would lose a lot of the end-user flexibility - the ability to define new entity types, add and remove fields or reconfigure those fields (changing their size or even their cardinality). Instead of adding new tables, you could add and remove columns from an existing table, but this brings other problems - your tables can become very large, and altering tables containing existing data is slow. Mutating a single table when you want to add/remove a field is a more dangerous tool to expose to an end-user than just adding/removing additional tables.

If only developers can do these things, then yes, you would expect to design your tables for optimal performance. There's a large body of knowledge on SQL optimisation that a developer would be able to bring into play. But if this requires giving up the ability of end-users to modify entity types via the GUI, it no longer achieves the original design goals. Drupal trades off best practice database design against delivering certain tools to end-users, and mitigates the performance impact with a fairly robust caching system. To me, that's good design, given the constraints.

You might argue that Drupal's design goals are wrong, or that those design goals make Drupal inappropriate for some use cases, but I don't think any of that makes it a fundamentally bad design.

EDIT: fixed some grammar in the db schema description

> This is pretty much the epitome of bad design. It creates a nightmare data model. The only advantage is that you can get away with this in the most brain-dead way possible when adding/removing fields without having to create a proper migration.

That's a pretty big advantage if you're an end-user who doesn't know how to create migrations. Yeah, you wouldn't do it that way if you were writing the code from scratch yourself, but you're not.

The 'nightmare data model' is only really a problem if you think of SQL as your level of abstraction. If you use the entity APIs, the data model looks fine. The whole point is that you shouldn't be poking around in the DB most of the time (I will admit that this is a principle frequently violated).

Entity storage in Drupal is pluggable, meaning that SQL databases are not the only possible storage. Tightly integrating SQL-specific design patterns would break pluggability (though again I would admit that SQL databases are by far the most common storage engines). Maybe making entity storage pluggable might be a bad idea, but it's not obviously bad design.

I'd imagine that other systems which can use SQL databases as dumb storage (Datomic does this, for instance) will probably not produce beautifully optimised tables and queries, because they're doing their optimisations at higher levels of abstraction. Drupal gives up thinking about tables and columns and instead thinks about entities and fields, which is how efficient caching of these entities is possible in the first place.

Sure, you or I could make something much more efficient by giving up a lot of this flexibility, but then you'd be looking at a different product with different design goals.

Sorry but you sound like you don't know what you're talking about.

> I don't think drupal had any actual design goals

You dont THINK? Well maybe you should LEARN and then you will KNOW.

Actually Drupal is very good (the best framework I know) for migration and interoperating with other software. But you don't do that by connecting DBs directly like you seems to want to do... (which is wrong with any framework/cms)