Translation by Column

Content management systems are... an interesting domain in software development. On one hand, they're one of the most basic types of CRUD applications, at least at their core. On the other, it's the sort of problem domain where you can get 90% of what you need really easily, but the remaining 10% are the cases that are going to make you pull your hair out.

Which is why pretty much every large CMS project supports some kind of plugin architecture, and usually some sort of marketplace to curate those plugins. That kind of curation is hard, writing plugins tends to be more about getting the feature I need" and less about releasing a reliable product", and thus the available plugins for most CMSes tend to be of wildly varying quality.

Paul recently installed a plugin for Joomla, and started receiving this error:

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Paul, like a good developer, checked with the plugin documentation to see if he could fix that error. The documentation had this to say:

you may have too many languages installed

This left Paul scratching his head. Sure, his CMS had eight different language packs installed, but how, exactly, was that too many"? It wasn't until he checked into the database schema that he understood what was going on:

This plugin's approach to handling multiple languages was simply to take every text field it needed to track and add a column to the table for every language. The result was a table with 231 columns, most of them language-specific duplicates.

Now, there are a few possible reasons why this is this way. It may simply be whoever wrote the plugin didn't know or care about setting up a meaningful lookup table. Maybe they were thinking about performance, and thought that well, if I denormalize I can get more data with a single query". Maybe they weren't thinking at all. Or maybe there's some quirk about creating your own tables as a Joomla plugin that the developer didn't want to create more tables than absolutely necessary.

Regardless, it's definitely one of the worst schemas you could come up with to handle localization.

Paul adds: I vote the developer for Best Database Designer of 2020."

[Advertisement] ProGet's got you covered with security and access controls on your NuGet feeds. Learn more.