Scalable Human Blog

The dilemma of choosing schema or schema-less databases. This post aims to simplify this key design decision.

Origins of Schema and Schema-less

The schema based databases has stood the test of time, in fact Relational Database Management Systems (RDBMS), have been a pioneer in this area.

Since the 1980s, and to the time of of this post, this type of database has been harnessed for:

• Financial records
• Manufacturing and logistical information
• Personnel data

And many other applications.

However, schema based databases are not perfect, in fact they did encounter issues during there time, which we continue to battle today. These issues include, the object-relational impedance mismatch between relational database and the object-oriented applications and many others that I will expand on later.

Additionally, there were attempts to address object-relational impedance mismatch issue with schema based databases, with the introduction of object database managements systems, which alleviated this issue particularly. An example of this is Object Relational Mapping (ORM) which in Java this is called the Java Persistence API, that is used for many enterprise Java applications.

Whilst this had success, the expanse of technology and challenges continued, such as horizontal scaling of node clusters. This resulted in the birth of NoSQL (schema-less databases).

The rise of NoSQL was during the 21st century and this gained popularity with the NoSQL hashtag, generally this was for those whom wanted to discuss new types of databases.

Furthermore, it is important to note that NoSQL databases are generally implemented with partial schemas, that are enforced by the Database Management System (DBMS) on a write basis, whilst with the integration of the application acting as an externalised schema on reads.

Although the name NoSQL does not have a direct connotations to what it is, it does however require it to be schema-less and typically the data store to be accessible using key value pairs.

Schema-less (NoSQL) Advantages

• Ability for rapid and easy scaling of servers (sharding/clustering) supported by most NoSQL technologies
  • E.g. Cassandra, Mongo, Hadoop
• None to little requirements to conform to a rigid schema
  • E.g. database, data types, etc
• No enforcement of data type limitations
• Ability to store all formats, including missing fields
• Ability to store both unstructured and structured data
• Faster to set up due to no schema model to be designed
• Generally less overhead and better performance
• Assists applications to be backward and forward compatible
• Some technologies allow for indexing, such as InfluxDB (time series database), where data that is inserted as a tag is indexed where data inserted by a field is not
• Developers have control of what objects (schema) they want to build with ease and on the fly, without the need to involve a Database Administrator (DBA)
• Popular solution for collecting mountains of application logs!
  • E.g. Splunk

Schema-less (NoSQL) Disadvantages

• All documents require parsing due to have no columns
  • This is mitigated as schema-less design is stored in memory
• Lack of metadata, the application is requires investigation to learn this information (field validation/data size and types)
• No control over what data goes in, generally no filters for bad data whilst getting loaded in either way
• Higher network traffic, due to sending entire documents
• Inability to apply database normalisation standards, can lead to higher data storage demands
• Lose out on functionality that SQL databases would give especially if you need automatic enforcement, referential integrity

Schema (SQL) Advantages

• Ability to apply security permission, this can be done by separating and protecting database objects based on user access rights
• Logical grouping of database objects
• Can make manipulating and accessing objects easier, especially if complex queries are required
  • NoSQL does not support relations between data types, running queries in NoSQL is doable, but much slower
• Transferable ownership of schemas
• Automatic enforcement of data type rules
• SQL database are better fit for heavy duty or complex transactions
• You can ensure ACID compliance (Atomicity, Consistency, Isolation, Durability) or defining exactly how transaction interact with database
• Ability to apply Normalisation standards for more efficient data storage, e.g. avoiding duplicate records

Schema (SQL) Disadvantages

• Added design time required to model the database initially and for any additional changes in the future
• Developers require involvement with DBAs to integrate the application
• Changing a database schema can be very impractical, especially if there are many changes and requirements for the system to maintain high availability
• Performance issues may occur if there are very large volumes of data, this is why most data intensive application opt for NoSQL
• Schema rigidity
  • Can be difficult to apply forward and backward compatibility within a node replication environment when rolling out application version updates across a node cluster

Conclusion

My final footnote, to warn this topic is highly variable when comparing schema and schema-less database systems, due to how different technologies have implemented this. Although, this pros and cons list will give you good gauge of what to consider when making the database design decision.

So just to round off, we can say that schema based databases are generally less flexible, but have added built in security characteristics. It is also noted, that the lack of flexibility is not entirely a disadvantage, this maybe more of an advantage due to the application use cases / requirements. Whilst in comparison, NoSQL is more preferable for those seeking more flexibility with the database and increased ability to handle larger volumes of data, with the added advantage of getting it set up much faster, although this needs to compared to the application requirements before, as one advantage can be a disadvantage to another with also the technology implementations. Which in turn, makes this pros and cons list more variable but a good source of understanding the differences.

📚 Further Reading & Related Topics

If you’re exploring schema-based SQL vs. schema-less NoSQL databases, these related articles will provide deeper insights:

• What is the Meaning of ACID in Databases? – Learn how SQL databases ensure transactional consistency through ACID compliance, contrasting with NoSQL’s flexibility.

• Distributed Databases: Is Performance, Scalability, and Transactional Guarantees Achievable? – Understand the trade-offs between SQL and NoSQL databases when balancing scalability, consistency, and performance.