TeraTech

The ColdFusion Experts: Develop | Secure | Optimize

  • Services
  • About
  • CF Alive
  • Blog
  • Podcast
  • Contact

  • Services
  • About
  • CF Alive
  • Blog
  • Podcast
  • Contact

SQL vs NoSQL (Top 4 Key Differences Explained)

May 25, 2020 By Michaela Light Leave a Comment

When developing a new application, you’ll face one key decision: what kind of database will your company’s app use?

There are two main flavors: SQL and NoSQL. You must choose wisely! 

Contents

  • How your database and app interact
  • What’s the difference between SQL and NoSQL?
  • SQL
  • NoSQL
  • Which one should you use?

I’ve seen enough botched database decisions as a ColdFusion consultant to know the long-term ramifications of choosing incorrectly between SQL or NoSQL. Your choice will affect the entire lifetime of your app’s existence, from its rollout to its expansion.

But what’s the difference between the two?

In this article, you will learn:

  1. How your database and app interact.
  2. The difference between SQL and NoSQL.
  3. Which one should you use?

Let’s talk about it!

How your database and app interact

Databases and apps are one of the more symbiotic relationships in the development world. One depends on the other, and if either goes wonky, the other will suffer. How do they work together?

Databases store the information your app digests, manipulates, and provides. Without a database, an app (often) doesn’t have a purpose.

A well-sorted database is the basic foundation for all worthwhile apps, whether they are basic payroll applications or larger registers of customer interactions and habits.

Over time, databases often gravitate towards disorder — as do most systems in the universe. The key is to manage:

  • The outer limits of your database’s size.
  • How much disorder your app can create.
  • How your database finds its way through the mess.

A mangled database or poorly structured app can lead to major slow-downs or even freezes.

What’s the difference between SQL and NoSQL?

So what’s the difference between SQL (often pronounced “sequel”) and NoSQL (pronounced… you guessed it, “no sequel”)? 

It’s actually very simple. Think of one as the very tidy, efficient stickler college roommate who had a space set aside for everything in the dorm and couldn’t tolerate disorder. The other is the manic, unholy mess of a roommate who’d leave their dirty and clean laundry in a big pile, sorted through as necessary.

Let’s find out which roommate leaves the dirty socks on the floor, and which is tidy.

SQL

Structured Query Language is a document management system (DMS) that’s standard for relational databases. This style of database stores information in tables, which can be cross-referenced and searched easily via columns and rows.

It is your tidy roommate. The one with the color-coordinated underwear drawer.

In the app world, this makes SQL ideal for:

  • augmenting existing records — good for searching, updating, editing, and inserting new information.
  • smaller-scale applications such as in-house systems to track expenses, employees, and legal paperwork.
  • retrieving data quickly via syntax and queries combined with well-designed tables.
  • complex queries which require a lot of data digging and manipulation.
  • apps that put a high value on accuracy.

There are downsides though. SQL is much harder to scale over the long term. As new varieties of data are added (a.k.a. more rows and columns added), the harder it becomes to manage and use efficiently. It can also limit apps to the boundaries of the database, meaning new types of data such as documents cannot become part of the database later on.

Thankfully, it has an alternative.

NoSQL

Where SQL may be a nerd keeping everything in neat order, NoSQL is the rampant artistic madman consuming up many types of disparate data to make bold, unexpected connections.

This, as you guessed, is the messy roommate who leaves the dirty dishes on the table. In the development world, we call them “non-relational” or “distributed” databases. (You’re probably familiar with some companies using NoSQL: Facebook, Uber and Google, to name a few.)

For apps, NoSQL is ideal when:

  • you need a non-relational document management system used for large stores of data.
  • your data is less structured and much more free-wheeling.
  • working extra-large operations and apps which distribute troves of data across many locations.
  • using several database technologies — not just a single table — to retrieve data and move it around.
  • using a wide variety of “data”, well beyond just numbers, documents or tables to include use graphs and key-value pairs (which allow hierarchies within data).
  • requiring maximum scalability, letting you add data to the existing structure without upsetting the existing order and tidiness.

All these traits combined make NoSQL perfect for simple queries of a mass trove of data. But these speed and scalability come at a price: NoSQL databases aren’t exactly accurate (just think how useful your Google search queries are, and you’ll see what I mean).

Which one should you use?

As a CIO overseeing an app’s creation, you will probably have to choose between one of these two database types. It may at first seem daunting: a decision that will determine the longer-term functioning of your app. But choose correctly and you won’t have to worry.

Here are a few questions you should ask:

  • What form does your data take? Tables and docs? Or snippets of data scattered across several sources?
    1. For tables and documents, use SQL.
    2. For scattered snippets, choose NoSQL.
  • If you structure your database in tables, with each row representing an entity such as “client” or “employee”, and every subsequent column an attribute, go with SQL.
  • If it’s a free-wheeling operation where data can come in many forms, graphs, documents, charts, tables, etc., go with NoSQL.
  • How will you structure that data?
    1. Rigidly structured offered by SQL is good for:
      • accounting, customer relations tools, and e-commerce.
      • adherents of ACID (atomicity, consistency, isolation, durability).
      • those who will maintain a tidy, efficient database with straightforward requirements.
    2. Multi-faceted data of different varieties used in NoSQL is ideal for:
      • apps which could grow over time to include a variety of data sources.
      • following the BASE model (Basic Availability, Soft state, Eventual consistency) of database management.
      • anyone who isn’t 100% sure their app will always use a table-based database. Even a “maybe” suggests you’re better suited for NoSQL, if only for the sake of future expansion and avoiding headaches.
  • What kind of queries will you run?
    1. SQL favors:
      • frequent queries
      • efficient execution
      • quick edits
      • less-technical users who can quickly learn basic query tactics
    2. NoSQL favors:
      • extra processing power — stronger machines
      • building querying functions into application layers, rather than database layers, in your multi-tier architecture.
      • developers and data scientists well-versed in handling complex queries of data.
  • How big will your collection of data get?
    1. Vertical scaling (a single server with expanding capacity) favors SQL, which does not scale easily.
    2. Horizontal scaling (multiple servers or even cloud computing, with almost limitless storage) is perfect for NoSQL. It’s very easy to scale.

I’ve found it’s best to have the character of your app and data match the database type. Voracious apps which will digest various types of data should marry perfectly with the non-discriminatory, all-encompassing nature of NoSQL.

Neat freak apps who only specialize in a limited data and care for little else should stick with SQL.

There are many decisions tied up in your app’s creation and development. Perhaps none as lasting as the structure of your database. Picking between SQL and NoSQL may seem simple, but it’s a choice one must make with caution and care.

Related Posts

  • 031 Going Modular With Fw/1 Subsystems 2.0, with Steven Neiland- Transcript031 Going Modular With Fw/1 Subsystems 2.0, with Steven Neiland- Transcript
  • Four leadership skills even Attila the Hun could have usedFour leadership skills even Attila the Hun could have used
  • ColdFusion Cluster Server Tuning training and consultingColdFusion Cluster Server Tuning training and consulting
  • State of the CF Union survey 2016 (powerful partial results)State of the CF Union survey 2016 (powerful partial results)
  • The Project AssistantThe Project Assistant
  • ColdFusion DevOps Explained (Faster and Easier Development)ColdFusion DevOps Explained (Faster and Easier Development)
  • Facebook
  • Twitter
  • LinkedIn

Filed Under: CF Maintenance, CF Training, CFML, ColdFusion, ColdFusion 2018, ColdFusion 2020, SQL

← Previous Post Into The Box 2020 Conference (Slides and Presentations)
Next Post → Is Adobe ColdFusion Dead? Programmers and CIOs Often Ask This Question

Popular podcast episodes

  • Revealing ColdFusion 2021 – Rakshith Naresh
  • CF and Angular – Nolan Erck
  • Migrating legacy CFML – Nolan Erck
  • Adobe API manager – Brian Sappey
  • Improve your CFML code – Kai Koenig

CF Alive Best Practices Checklist

 

Modern ColdFusion development best practices that reduce stress, inefficiency, project lifecycle costs while simultaneously increasing project velocity and innovation.

Top articles

  • CF Hosting (independent guide)
  • What is Adobe ColdFusion
  • Is Lucee CFML now better than ACF?
  • Is CF dead?
  • Learn CF (comprehensive list of resources)

Recent Posts

  • Funny ColdFusion Custom Tag Competition (CFML Programmer’s Jokes)
  • Protected: State of the CF Union 2021 Survey Results
  • ColdFusion Development: Less Coding for Faster Turnaround
  • Protected: State of the CF Union 2021 Survey Released
  • ColdFusion Server Crashing – First 3 Steps to Fix it Fast

Categories

  • ActionScript
  • Adobe CF Summit
  • Adobe CF Summit East
  • Adobe CF Summit East 2018
  • Adobe ColdFusion 11
  • Adobe ColdFusion 2020 Beta
  • Adobe ColdFusion 2021
  • Adobe ColdFusion Project Stratus
  • Adobe ColdFusion Security
  • AIR
  • Ajax
  • AngularJS
  • Announcement
  • API
  • Apollo
  • Auto Security Lockdown
  • AWS
  • C#
  • Certification
  • CF Alive
  • CF Alive Book
  • CF Alive Podcast
  • CF Camp
  • CF Developer week
  • CF Maintenance
  • CF Summit India
  • CF Tags
  • CF Training
  • CF Vs. Other Languages
  • CFEclipse
  • CFML
  • CFML Open- Source
  • CFObjective
  • cfquery
  • CFSummit
  • CFUnited
  • China Chopper
  • CIO
  • Classes
  • Client Highlights
  • ColdBox
  • ColdFusion
  • ColdFusion 2018
  • ColdFusion 2020
  • ColdFusion 2021
  • ColdFusion 9
  • ColdFusion community
  • ColdFusion Conference
  • ColdFusion Consulting
  • ColdFusion Developer
  • ColdFusion Development
  • ColdFusion Hosting
  • ColdFusion Roadmap
  • ColdFusion Security
  • ColdFusion Webinar
  • CommandBox
  • Conference
  • Cool Stuff
  • Culture
  • Cybercrime
  • Database
  • Development Approach
  • DevOps
  • Docker
  • Fixinator
  • Flex
  • Frameworks
  • Fusebox
  • FusionReactor
  • Futurology
  • Garbage Collector
  • Google Down
  • Into The Box Latam
  • IntoTheBox Conference
  • Java
  • JavaScript
  • JVM
  • Learn CFML
  • Learn ColdFusion
  • Legacy Code
  • Load Testing
  • Lucee
  • Management
  • MAX
  • MDCFUG Lunch
  • Microsoft Azure
  • Mindmapping
  • MockBox
  • Modernize ColdFusion
  • Monitoring
  • Muracon
  • NCDevCon
  • New Intern
  • News
  • Node.js
  • Open- Source
  • ORM
  • Ortus Developer Week
  • Ortus Roadshow
  • Performance
  • Performance Tuning
  • PHP
  • Productivity
  • Programming Languages
  • Project planning
  • Query of Queries
  • Scalability
  • Security
  • Server Crash
  • Server Software
  • Server Tuning
  • Social Media
  • Spiral Web
  • SQL
  • Success Story
  • Survey
  • Technology
  • TestBox
  • Tips
  • Transcript
  • Trapeze Development
  • Uncategorized
  • Web 2.0
  • Web Application
  • Web Server
  • Webinar
  • Webmail
  • What is ColdFusion?
  • Whole Brain Development
  • Women in Tech
  • Work From Home
  • Home
  • Services
  • About Us
  • CF Alive
    • CF Alive Book
    • CF Alive Inner Circle
    • CF Alive full resources cheatsheet
  • Blog
  • Podcast
    • Podcast Guest schedule
  • Contact
  • Sitemap

The ColdFusion Experts:
Develop, Secure, Optimize

TeraTech Inc
451 Hungerford Drive Suite 119
Rockville, MD 20850

Tel : +1 (301) 424 3903
Fax: +1 (301) 762 8185

Follow us on Facebook Follow us on LinkedIn Follow us on Twitter Follow us on Pinterest Follow us on YouTube

Copyright © 1998–2021 TeraTech Inc. All rights Reserved.