To SQL or not to SQL?

Relational databases are established solutions, but there are alternatives

Eric Lai wrote an interesting article for Computerworld entitled No to SQL? Anti-database movement gains steam that highlighted the small but growing trend for not using a traditional relational database for managing data. Nati Shalom's No to SQL? Anti-database movement gains steam - My Take is a good follow-up.

I'm sure that most of us have good experiences of using a relational database in our projects and, despite the pain of sometimes mapping the data into a relational schema, relational databases provide an easy to use known quantity for managing data. With this in mind, you should certainly let your experience guide you but bear in mind that relational databases aren't the answer to every question.

The case study that we use as a basis for the exercises in our software architecture training course is relatively small yet most people decide to use a relational database to store the data without really giving the alternatives a second thought. Most of the time it comes down to experience in that most people are comfortable with using a relational database in their architecture. But there are many viable alternatives; from using flat files and object databases through to in-memory data structures, data grids and the cloud. So while relational databases might be a solution for your particular problem, it's always worth spending a couple of minutes assessing whether they are the *best* solution. Here are some things to think about before deciding on whether to go down the SQL or non-SQL route.

  • Do you have special non-functional requirements that would be hard to satisfy with a relational database? (e.g. high performance/low latency, massive scalability, etc)
  • What is the available skillset of the team?
  • Do you have existing licenses for an RDBMS?
  • Could an open source RDBMS be appropriate?
  • Do you need to access legacy systems where the data is already in an RDBMS?
  • Do you have the hardware available to run an RDBMS?
  • Do you already have existing backup and archival processes and procedures for relational databases?
  • What are your management information and reporting requirements (scheduled and ad hoc)? Is it possible to satisfy these with a non-SQL solution?
  • Do other systems need access to your data via a SQL interface? (a service gateway might be a better approach, but that's another issue)
  • Do you have data migration requirements from an existing relational database?
  • Can the data management problem be split up into transactional and non-transactional partitions, maybe using a relational database for only one of them?
  • Do you really *need* persistence?

Relational databases are established mainstream solutions that are applicable in many cases. Just don't forget that there are other alternatives too.

About the author

Simon is an independent consultant specializing in software architecture, and the author of Software Architecture for Developers (a developer-friendly guide to software architecture, technical leadership and the balance with agility). He’s also the creator of the C4 software architecture model and the founder of Structurizr, which is a collection of open source and commercial tooling to help software teams visualise, document and explore their software architecture.

You can find Simon on Twitter at @simonbrown ... see simonbrown.je for information about his speaking schedule, videos from past conferences and software architecture training.



Re: To SQL or not to SQL?

There's no doubt that a relational database is a golden hammer in many application architectures, often being the basis for all claims of scalability and resilience.

While it might seem like a lazy solution, it's also a simple solution, using what's there, proven and, in many cases, already paid for and managed.

Having said that, I've definitely seen traditional RDBMS usage being complemented with in-memory, object and analytical databases more in the past year. As you say, the trick is to select the right mix of tools for the job.

The case study in the training course is a good example as it's not a system of record and the data of interest are aggregates. Perfect technical solution or boiler-plate SQL application? I know what we chose when it was actually implemented ;)

Re: To SQL or not to SQL?

It is easy to say that you should use the best tool for the job but at the same time you don't want to use too many tools. At my previous job we used to store our relational data in Oracle and our spatial data in ESRI. However Oracle improved its spatial abilities and we decided to store our spatial data in Oracle too, no more ESRI. Having one db instead of two made our life much easier. We now can store spatial data and relational data in the same table and we can insert and select relational data and spatial data with one SQL statement. The spatial abilities of ESRI are probably better than Oracle's spatial abilities but putting all our data in just one db has huge benefits too.

Re: To SQL or not to SQL?

"high performance/low latency, massive scalability" hard to obtain with rdbms? Since when? I have never had a problem, even at terabytes of data and insanely complex sql queries. You just got to know what you're doing, that's all.

Re: To SQL or not to SQL?

I agree. Nearly anything is possible, you often just need to tackle the problem differently (e.g. by using techniques such as partitioning, sharding, write behind caching, etc). The point I'm trying to make is that there are sometimes better approaches than using a relational database, yet people venture down this road just because it's familiar.

Add a comment Send a TrackBack