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.