If you’re reading this, you already know enough about web application development to be cautious when selecting your database. The better you name the specific goal of your application, the more likely you’ll be to make the smart choice. Read on to learn more about database types and the strengths and weaknesses of each.
The “Four Ss” of Databases
No database can meet the needs of every application. The “Four Ss” of database characteristics are structure, size, speed, and scalability. The more you can pin down your application’s needs for each, the easier you’ll find your ideal database. While workarounds are often possible, they tend to add complexity and development time and do not always provide reliable results.
The storage of data ranges on a continuum between structured and unstructured, semi-structured falling between the two. structured, semi-structured, or unstructured. The more structured the data, the more readily it can be accessed and analyzed.
Highly structured data tends to be strictly quantitative. It includes numbers, dates, ratings, and other types of easily organized information. It is easily stored, searched, and analyzed.
Unstructured data is more complex and includes video, audio, social media posts, and other types of highly variable data. It is more difficult to store, search, and analyze.
Semi-structured data is data that falls somewhere on the highly structured-unstructured spectrum. Examples include email, CSV, JSON, and RDF, among many others.
Size describes the quantity of data to be stored and retrieved. The ability of a database to partition data across multiple file systems and servers
Naturally, most developers will want as much speed as possible. However, some databases are optimized for read-heavy apps, while others are designed more for write-heavy ones.
Some databases grow with your business easier than others. Take the time to decide whether your app calls for a horizontal or vertical scaling.
Horizontal scaling refers to adding more servers, while vertical scaling means adding more resources to existing servers. Most databases lend themselves to one or the other, and each has different challenges.
Having Trouble Nailing Down Your App? Ask These 7 Questions.
- What specific business goal does your application seek to achieve?
- What type of data will you store?
- What’s more important: long term data storage or high data-insert rate?
- How many queries per min/hour/day?
- What coding language are you using?
- Do you need clear relationships between data sets, or something more flexible?
- How important is scalability to you?
Is a SQL-Based Solution Right for You?
Relational database management systems (RDBMS), most of which are based on Structured Query Language (SQL). In short, if your data in structured and your business isn’t growing too fast, consider SQL.
If your app will utilize structured data, then SQL provides many advantages. Many implementations are available (MySQL, Oracle, PostgreSQL, and so on), but the standardized language makes it predictable and reliable.
Data is stored in tables with rows and columns and is usually quantitative in nature, so data is easily stored and quickly retrieved for queries.
The trade-off is scalability. SQL-based solutions are notoriously difficult to do well. The industry defines “well” as ACID-compliance (Atomicity, Consistency, Isolation, Durability), which in essence refers to the integrity of your database.
- Highly structured data
- Data is easily stored and retrieved
- Suited for varying user permissions
- Unsuitable for semi-structured or unstructured data
- Cost more than non-relational database systems, particularly when scaling
- Applications where data integrity is essential, like ecommerce, financial apps, defense, security, medical records
- Automated assistance for customers
- Quantitative data
- Automation of internal processes
NoSQL (Non-Relational) Database Solutions
If SQL isn’t the answer, then your choice goes beyond “NoSQL.” Possibilities for a non-relational database include document, key value, and wide column.
Document non-relational databases store data in JSON, BSON, or XML. Examples include MongoDB, Cassandra, Redi, Apache, and Couchbase, among many others. Documents can contain data of any type, and it uses a flexible scheme that does not enforce document structure.
- Flexible; can handle structured and unstructured data
- Users can change documents without affecting other documents
- Fast write-speed
- Easy to scale horizontally
- Inter-document querying not possible
- Weaker ACID compliance
- Situations when developers are unsure about the nature of incoming data
- Rapid prototyping
- Content management
- Data analysis
This database takes its name from associating each value with a specific key. This key is a unique identifier associated only with the value, and can be anything allowed by the database management system.
Values are stored as blogs and don’t need predefined schema. They can take almost any form, such as numbers, strings, counters, JSON, HTML, short videos, lists, and so on. Redis and Memcached are two of the most well known examples of Key Value databases.
- Flexibility and performance; keys prevent the need for index searching
- Portability; key value stores can be moved without having to write new code
- Horizontal scalability
- Lower operating costs
- Impossible to query values because data is stored as blogs
- Difficult reporting
- Difficult to edit parts of values
- User profiles or settings
- Product reviews
- Blog comments
- Data that is accessed often, but rarely changed
Wide Column databases are sometimes considered to be a sub-type of key value stores, but they share some qualities with RDBMSs. These databases use keyspaces instead of schemas, and these keyspaces surround column families. These families are similar to the tables common to RDBMSs, but are more flexible in structure.
Examples include Cassanda and HBase, among others.
- Compresses better than row-based systems
- Easy to update in bulk
- Difficult to update individual records
- Slower to handle transactions that RDBMSs
- Big data analytics that prioritize speed
- Big data warehousing