Database design: when in doubt – use meta tables

It’s becoming quiet intriguing when you work with the 3rd party closed-source projects. You can see how the project was maturing, and how things were changing on the fly, especially when you have to plug in some extra logic on top of it, and the code is obfuscated, so the only way how to find all ins and outs – check the database schema.

Certain systems force to develop evil workarounds to coupe with bad design practices, whether it’s code or database wise.

Apparently, in the beginning of the project, dev’s came out with one-storage solution for holding the prices of products and domains, which is quiet obvious, as we shouldn’t re-invent any mystical entities to differentiate the concept of the price. As the time fly, the notion of domain pricing strategies changed, as well as the billing/recurring amounts were introduced. In order not to break the compatibility of the previous versions (migrations scripts, duh? major version notifications, duh?), they kept the design and grew the table horizontally.

pricing layout of db
Pricing layout

The end result got completely irrelevant in the ‘naming’ of the table. For instance, on the screenshot, we have 11.00 USD for registering domain for 10 years. On the backend side in lands into ‘biennially’ field. Logic? Nah, who needs that?!

So, what happened here is the following – when the developers ran out of the fields to store the year count for the domain registration, they just stated: “Whatever is in the pricing table except testsetupfee, will represent years of domain registration”.

Personally, it’s a classical example of meta tables design. When you don’t know how you’d scale the db table horizontally, and what kind of fields it’ll include – go for key/value table design. Get a set of meaningful (yeah!) option_key’s store the values. If you’re concerned with the performance on this matter – don’t forget to consider the fact, that it’s just a prices table. For each product/domain entity you might have 10-100 records, depends how sophisticated your pricing model would go, but don’t break the fields notation that badly!

Good example of meta tables – WordPress wp_options design, and good execution wrappers on top – solves it all. If you’re insisting on horizontal table scaling – not a problem at all – just think about what you’ll end up with by the end of the day, and migrate it from meta tables after all.

2 thoughts on “Database design: when in doubt – use meta tables”

  1. Obfuscation reminded me a few 3rd party systems that I had to migrate away from, where even the database was obfuscated: using md5 checksums (or similar) for table names, field names, and ID values. Pretty much any SQL query looked like The Matrix screensaver. 🙂

    As for the key-value tables – sure, they are pretty useful. But they have their own problems. Just for storing data – it’s fine. But if you need to validate or do any fancy querying, it gets out of hand pretty fast too.

    1. The lack of obfuscation for the given project would have boosted the development of the project to the speed of light, but unfortunately the community is stuck with handling not obvious issues with plugin system, and reverse engineer tricky things like the one I mentioned in the post.

      For the key/value storage – agree – the fact of fancy querying is the pivot point, of either good wrapping, or considering to refactor the table to horizontal structure. By that time you should collect enough of data to realise how to name and manipulate them.

Leave a Reply