Data Royale: [SQL] vs noSQL

This is Part One in a two-part series about the differences between relational and non-relational databases.

It's been a productive year to say the least, and after having built multiple apps from the bottom-up, up-to-bottom, sideways, diagonal, to criss-cross apple sauce, throughout all these methods, there is one thing that stays the same across the board.

Spoiler Alert: It's the act(read:art) of planning.

Whether in a notebook or a digital document, best practice is to plan a project all the way down to the nitty gritty, then, plan up from there. It only took a few and a half failures for me to realize that jumping headfirst into a project not only makes it more difficult to manage in the long run, but also it's more likely that I won't finish it.

Now, whenever I have an idea, before I draw up a wire frame or choose a tech stack, the first thing I always do is figure out how I'm going to divvy up my data.

Battle Royale: Relational vs. Non-Relational

Beneath the surface, there is a deeper divide than the semantics of SQL(Structured Query Language) vs. noSQL, and that's really how, and also if data in the app is related.

The beauty of both SQL and noSQL methodologies is that they're both perfect for persisting data, the method used just depends on the needs of the app.

---

Relational: SQL

Relational databases are made up of a network of tables, these tables hold columns and fields and in many cases, hold references to each other.

Data Modeling

Whether relational or non-relational, It's always helpful to be able to see a physical representation of these connections to really grasp what our information is doing in the background. To achieve this, we should draw up a model of our data, but of course, even that should be planned first!

I find that it greatly helps to flesh out all of the data on paper or whiteboard, typing can be almost mindless at times whereas the act writing (especially in cursive!) requires some additional thought, and therefore, more mindfulness goes into the work, which in turn can cut down on the amount keystrokes and production drafts.

Plan each table, then, plan them again!

images via FileMaker

Relational databases are highly structured which makes them quickly scalable, and follow an ACID (Atomicity, Consistency, Isolation and Durability) theorem. Using SQL to query a relational database can be restrictive, but it also proves to be incredibly useful and is extremely powerful when performing complex operations.

Due to these restrictions, it's important to resolve these interactions early on as to avoid rework and overhaul disasters during building and production.

Once tables with columns and their corresponding fields are fleshed out, we can see each table's connection to another in the database, and which fields in each table are related.

As soon as the hard copy is finished, I recommend creating a digital one using a design tool, my favorite is DB Designer.

Which One?

Once the data is relatively (no pun intended) structured, this is the time to choose which Relational Database Management System(RDBMS) to use to perform operations on the database. The amount of RBDMS's are many and of course some are more popular than others but again, and some are very similar to others. For example, the stark differences between MySQL and mariaDB can be vague, mariaDB just happens to be a fork of MySQL. Choice can and really should be made base on what's best for the app.

In Conclusion

Relational databases are great when you have information that, surprise, is related in some way. These structured systems are adept at storing data in a manner that makes retrieval methods a breeze, relatively speaking(har-har).

A little bit of a downside to this structure is that without thorough planning early on, it can be a pain trying to account for unexpected inputs later, which is why it's a great idea to do multiple early drafts to avoid these pitfalls.

However, I find the greatest pro to be that RDBMs can be chosen strictly based on what an app needs, so a lot of the guesswork can be removed and tech can be decided on objectively.

Stay tuned for next week when I go through the ins and outs of a Non-relational database. In the meantime, what's your favorite RDBMS? It's PostGreSQL for me!

Planning a relational database? Check out this helpful resource from FileMaker for additional insight.

Thanks for reading!

Comments

[geraldew]

This is incorrect where it says:
"For example, the biggest difference between mySQL and mariaDB, is that mariaDB is open source!"

That is not true as both are available under Open Source licenses (and I just did a quick check to be sure).