2

Usually there is a 1-1 mapping between an object in the application layer and a table in the database layer.
This coupling leads to complexities whenever there is a database schema change and the application version may not be compatible with the current database version that it is talking to.
I was reading that there is an approach of database design that is driven by metadata. Something like the fields available for an object is found from a table and then a query is done. So some form of direction in order to avoid versioning problems.
Is this approach used in general and how exactly is it implemented? Also are there are other approaches to deal with this issue?

4

The approach you're think of is called Entity-Attribute=Value (EAV). If you Google around for it you'll find very quickly that it is almost universally reviled. In short, it's generally a bad idea because it doesn't let your relational database management system do what it was designed to do.

As an alternative, you should think about schema changes as something that you need to keep backward compatible. This often means that new columns will be nullable or have defaults set. Depending on what you want to do with your application, though, sometimes a database change is simply a breaking change and you can't release the database change without releasing the corresponding code change.

I've seen some people keep a short code table in their database that contains version information in it (e.g. minimum and maximum supported code versions). The idea with this is that when your application starts it does a quick lookup in the code table and if the database version isn't compatible with the code the code exits gracefully.

  • Thank you and upvoted! If new columns are added then isn't the change backwards compatible by default? Previous app version would just not be using the columns as they would be unknown. Or am I missing something? But what about removing columns? That is what I am trying to address. I'll read into the EAV but for my case I don't really need the relational part due to not having many tables of FK constraints and I mostly focused on app upgrades and performance. Would that be a good use case for EAV? – Jim Nov 25 '18 at 9:11
  • 1
    @Jim If you add a mandatory column without a default that would be a breaking change. If you add a column that needs to be populated to support a downstream system (say a reporting system) then that could be a breaking change. Removing a column is likely to be a breaking change. The only way to avoid it that I can think of would be to trap for the error a missing column would generate and degrade gracefully, or even continue running. I don't know that I'd do something like that though, it seems too risky and a lot of work for something that hopefully is very rare. – Joel Brown Nov 25 '18 at 10:08
  • @Jim As for good use cases for EAV, I've argued here and here that "EAV is Evil" - except when it isn't. For example in an online shopping product catalog. – Joel Brown Nov 25 '18 at 10:10
  • Ah I see. By trap for the error a missing column would generate you mean like a try/catch in the app level? – Jim Nov 25 '18 at 10:27
  • Also I found this example for EAV blog.greglow.com/2018/02/12/… but does not seem to take into account the version of the object (e.g. People). Is versioning not something addressed by EAV? I am confused in that aspect – Jim Nov 25 '18 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.