There are so many different kinds of databases available today, to the point that sometimes it can be tricky to decide which is the best option for a project. So many possibilities!
Some time ago, I decided to make a compilation for myself, to clear up my mind regarding the possibilities available to me.
This compilation lists types, advantages/disadvantages and use cases of different databases from my ignorant point of view.
Definitions
Types of databases according to their logical structure
- Relational databases:
- These are the databases that we are all familiar with. Data is organized in tables, and there is a defined structure or chema which specifies the columns in each table, the relationships between tables, etc. The database can be manipulated and queried through SQL statements or queries. For example, MySQL is a relational database management system.
- XML databases:
- The XML format is used to store and represent data that has a hierarchical structure, which would be a complex problem in a relational database. Another advantage of using XML is that it is a platform-independent language. As opposed to relational databases, two "rows" of the same "table" in a XML database can have a different number of columns. Android applications normally use XML databases (and SQLite databases as well).
- Object-oriented databases:
- The data is saved directly as objects organized in collections. You can use a Query Language to manipulate them. Matisse is an example of object-oriented database management system.
- Document-oriented databases:
- The data doesn't have a defined structure. It is stored in text files, in a simple key-value format. Again, there are no tables but "collections", and there are no rows but "documents". In fact, documents have a wider meaning compared to rows, since there can be nested documents. In XML there was a lot of noise (tags) and few information, while here, a more direct syntax like JSON or YAML is used. MongoDB is an example of a DOD.
Ways to link a database with an application
- Embedded databases:
- They are integrated with the application, which means they are run inside the application. For example: in Java, the database is run in the JVM. Apache Derby can allow you to do that.
- Object-Relational Mapping:
- The mapping turns the databases's schema into a structure of classes, where each table is a class and its columns are attributes of that class. Once the data is manipulated, it is stored again in the database. The conversion between one representation system and the other is totally transparent to the programmer. Hibernate ORM is an example of object-relational mapping framework for Java.
Advantages and disadvantages
| Advantages | Disadvantages | |
|---|---|---|
| Relational | Referencial integrity is guaranteed.
No need to load the entire database in memory to work with it. It comes with security mechanisms out of the box. |
They can be slow and expensive.
It is complex to work with hierarchical data structures, since there are so many relationships to define. The schema is fixed. |
| XML | More flexible (no schema). Less complexity.
Scalable. You can write less code in XQuery. |
Need to load the whole file in order to work with it.
Not recommended to store huge amounts of data. No consistency. No transactions. |
| Obj. oriented | Easy to maintain.
Flexibles, no schema, greater modelling capacity. Extensible. Adapted to complex applications, they improve performance. |
Still not widely adopted, limited, not evaluated nor improved yet.
Need to know the implementation used to access the database in order to make a query, which goes against encapsulation in OO. No security, objects don't have access rights. |
| Doc. oriented | Horizontally scalable.
Flexible, no schema (or it's variable), no data types. Simpler design. ACID guaranteed, like relationals. |
Data duplication is so easy.
Data integrity can be lost. In exchange for flexibility we loose many of the things that a relational database guarantees. Joins are made manually, which can be complex. |
| Embedded | Independent of the databases's storing mechanism (Derby, mySQL, etc.).
No need to run a server to use them, they are integrated in the application. |
Not scalable.
Problems for high concurrency scenarios. Can't stand high volumes of read/write access. |
| Mapping | Abstracts you from the database.
The code you write (specially queries) is simpler. It's easy to port the application to a different database. |
It's difficult to see what's going on "behind the scenes" because of the huge abstraction.
Can lead to bad-designed databases. No ad hoc queries. It takes a long time to make a query. Loss of performance and efficiency. |
Use cases
- Relational databases:
- Applications that need a high performance, or where data loss is not allowed. When you need to store huge amounts of data in a consistent way. When you need heavy data processing or ad hoc queries. They work better when you have few fields (columns).
- XML databases:
- The biggest use is to export or to transfer data between applications. You can also use it for applications that need to handle a constant flow of reads/writes of data with a hierarchical structure. If you have to work with a lot of fields and you don't care about possible integrity errors, XML is easier to use than a relational database.
- Object-oriented databases:
- Low scale dynamic and interactive applications. Applications with complex models, since it provides a fast access through key-value pairs.
- Document-oriented databases:
- Applications for non-structured data, in huge amounts, and, like XML, requiring a a constant flow of reads/writes.
- Embedded databases:
- Not recommended for web applications, since you can lose everything if something fails. Use in applications where the data load is not huge and high availability is not a priority.
- Object-Relational Mapping:
- Should be used when you want to take advantage of transparent persistence and use a relational database. When you want to reduce your code and, through caching, improve performance over using an embedded SQL.
Do you agree with these points? Do you have anything to add? Please feel free to make your contribution in the comments!