Database Selection & Design (Part I)

— Introduction to Database Technologies —

Pre — Relational (SQL) Databases:

  • Very efficient in handling highly structured data
  • String ACID (Atomicity, Consistency, Isolation, and Durability) properties
  • Data is easily stored and retrieved using SQL queries. The structure can be scaled up quickly because adding data without modifying existing data is simple.
  • Well-suited to applications that require tiered access, since RDBMS has inbuilt structure of access controls. i.e.. customers could view their accounts while company representatives could both view and update.
  • Very difficult to handle and support unstructured data
  • Cost is a consideration with relational databases, as they tend to be more expensive to set up and grow
  • Representing real world entities in context is difficult in the bounds of an RDBMS. “Sliced” data has to be reassembled from tables into something more readable, and speed can be negatively impacted. The fixed schema doesn’t react well to change, either.
  • The structure of relational database is well suited for vertical scaling. Horizontal scaling is usually faster and economical. Sharding (in which data is horizontally partitioned and distributed across a collection of machines) is necessary to scale out a relational database. Sharding relational databases while maintaining ACID compliance can be a challenge

Post — Relational (NoSQL) Databases:

  • This style of database has a lot of positives. It’s incredibly flexible, able to handle a very wide array of data types easily. Keys are used to go straight to the value with no index searching or joins, so performance is high.
  • Portability is another benefit: key-value stores can be moved from one system to another without rewriting code. Finally, they’re highly horizontally scalable and have lower operating costs overall.
  • Flexibility comes at a price. It’s impossible to query values, because they’re stored as a blob and can only be returned as such. This makes it hard to do reporting or edit parts of values. Not all objects are easy to model as key-value pairs, either.
  • Document stores are very flexible. They handle semi-structured and unstructured data well. When you don’t know what type of data will be stored during set-up, document stores are a good choice. You can create your desired structure in a particular document without affecting all documents.
  • Schema can be modified without causing downtime, which leads to high availability. Write speed is generally fast as well.
  • Easy to scale horizontally. Document stores scale out fast and efficiently
  • Document databases sacrifice ACID compliance for flexibility. While querying can be performed within a document, it’s not possible across documents
  • This type of database has some benefits of both relational and non-relational databases. It deals better with both structured and semi-structured data than other non-relational databases, and it’s easier to update.
  • Compared to relational databases, it’s more horizontally scalable and faster at scale.
  • Column databases compress better than row-based systems.
  • Large data sets are simple to explore. Wide-column stores are particularly good at aggregation queries
  • Writes are expensive in the small. While updating is easy to do in bulk, uploading and updating individual records is hard.
  • Slower than relational databases when handling transactions.

Conclusion:

Database has grown to great extends in the last 50+ years and will continue to grow based on the ever changing trends in the way of life. Having this information organized and processed on time, gives us the ability to make sense of what is happening around us and the business opportunities it opens up for us to pursue.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Faisal Mohamed

Engineering Director, People Leader, Offroader, Handyman, Movie Buff, Photographer, Gardener