What is 'impedence mismatch' in the context of sql databases ?

I came across this term somewhere while trying to understand differences between sql and nosql databases. Tried searching more about it but couldn't get it. I'm unable to understand how relational data model to object data model conversion happens and why is it costly ? How exactly nosql databases eliminate this problem ?

Comments

[Dian Fay]

More fully, it's the "object-relational impedance mismatch". This refers to the central problem of using an object-relational mapper (O/RM) framework to handle data access and manipulation. O/RMs require you to implement a parallel data model in your application code: if you have a users table, you'll have to write a User class which represents records in that table, and so forth.

The issue is that relational databases work in a fundamentally different manner from application code. A relational database cares about two things: records, and relationships between records as denoted by foreign key constraints. Application code works with all manner of more complicated data structures and techniques: hierarchies, iteration, graph traversal. So when you design a second, sort-of-but-not-completely parallel data model that works for your application code, it's not so easy to translate that into the existing data model expressed by your database schema.

If your users table (and hence your User model) has a visits field and you realize that you'd been double-counting visits, you might want to amend the data you've collected so far. With SQL, you'd UPDATE users SET visits = visits / 2; -- fairly simple. However, if you're using an O/RM and don't want to write SQL, you'll likely have to load each User into memory, halve its visits, and persist it back. This takes much, much more time and makes two round-trips to the database for each record in the users table. It's much quicker to abandon the O/RM and go with the SQL query, and indeed most O/RMs offer the ability to run raw SQL because the impedance mismatch is not a problem you can beat head-on.

The impedance mismatch is a problem characteristic of O/RMs specifically. There's nothing special about NoSQL databases which eliminates it, and in fact it's quite possible to work with a relational database without running into it: you just have to choose a data access framework which isn't an O/RM. Note that the opposite is also true: if you use an "O/RM" for a NoSQL database, like Mongoose for MongoDB, you can still run into the impedance mismatch. Look into data mappers if you still want to use a relational database -- I highly recommend at least investigating it unless you know that your data architecture requires specialized storage, since relational databases are much more generally useful than NoSQL stores.

Ted Neward's blog post The Vietnam of Computer Science goes into more detail, and if you have a SkillsMatter account I talked about the impedance mismatch in the course of describing why my own project Massive.js isn't an O/RM at FullStack London last week.

[Ankit Utekar]

Thank you for such a thorough clarification!

[Dian Fay]

Table inheritance is a useful feature for a particular kind of scenario. It won't save you from the impedance mismatch, which has much broader causes and implications.