Database Design and Management
What is a database, exactly? Databases are critical software systems that help organize, store, and access data. While theoretically, large amounts of data could be stored in a file, databases are more commonly used for their built-in resiliency and concurrent data access capabilities.
Resiliency ensures that data writes are fully committed, and built-in concurrent data access allows hundreds or even thousands of people to access and write to a shared database, all at the same time.
Properly designing database systems is essential to their utility. Once a database is created, it’s very difficult to change the underlying structure. This is particularly true once multiple applications are accessing the database and relying on its information.
Once a database is designed, maintaining and safeguarding both the data it contains and access to that data becomes critical. In addition, updating the system as needed to keep things running smoothly is essential to the continued efficacy of the database and any associated applications.
Types of Databases
There are many kinds of databases, but the most notable ones are relational databases and NoSQL databases, such as document databases and graph databases.
Relational databases have been the backbone of software systems for over 50 years. They are highly structured with a primary focus on data integrity and organization.
NoSQL databases, on the other hand, are relatively new. These databases first saw traction a decade ago, when they began to grow along with “big data.” There are four types of NoSQL databases, but the main two are document databases and graph databases.
Document databases, like Mongo DB, are used for storing unstructured data (or lightly structured data). For example, consider an email. Emails contain some basic “header” information that is structured, like the subject line, sender, and recipient. The content of the email can be anything and is thus far less structured.
In contrast, the aptly named graph database, like and Neo4j, works off a graph model. This type focuses on relational information in the form of graph entities and the relationships between them. Think about, for instance, a social media friend list graph: Person A knows Person B, who knows Person C, who knows Person D, who knows Person A. Graph databases are extremely powerful and give a unique perspective of data relations. Use cases for graph databases include in fraud detection and prevention, criminal or terrorist activity analysis, and more.
Some databases are highly specialized, such as a Time Series Database (TSDB), which deals specifically with data over time. Examples of time series databases include Prometheus and Loki for system monitoring and SRE (Site Reliability Engineering) operations. Flat file databases, which are like Excel or .csv files, are only loosely considered to be a database.
Database Design Principles
Database software can be divided into three parts. The first is the database itself, which is the storage aspect where all the data lives once it’s written to disk. The second is the database engine, which is the software that’s responsible for managing access to the data, reading the data, etc.
The third aspect is the user interface, where end-users interact with the database engine and the data. User Interfaces can be IDEs (SSMS, MySQL Workbench) or CLIs (console apps for commands).
Database Design and Development
Designing and developing a database is critical, as it is difficult to change the underlying structure once they’re populated with data and potentially dozens of applications are using them. A well-designed database may be able to handle millions of data points per second without breaking a sweat, whereas a poor one would buckle and collapse under that kind of pressure.
Often, companies will hire amateur database developers, who create a system that can only grow to a certain point before they have to be rebuilt. A well-designed database should be able to evolve to almost any point without needing major rework.
Design practices vary significantly based on the type of database and the primary use of the data. Design practices vary significantly based on the type of database and on the primary use of the data. For example, a company may keep both OLTP and OLAP data in a relational database, but the design of those relational databases would be quite different.
Examples of Databases
If you are looking for database software examples, the below table outlines some well-known options, organized by type.
| Type | Name |
| Relational | Microsoft SQL Server |
| Relational | MySQL\MariaDB |
| Relational | PostgreSQL |
| Relational | SQLite |
| NoSQL\Document | MongoDB |
| NoSQL\Graph | Neo4j |
| Time Series | Prometheus & Loki |
Conclusion
In conclusion, databases drive modern civilization. Everything we do, from Netflix to law enforcement to even our cars, is driven by a database somewhere. Databases are critical for organizing, storing, and accessing data, and they tend to have built-in resiliency and concurrent data access capabilities. There are many kinds of databases, and each has its own unique strengths and use cases. Designing and developing a well-structured database is critical, as it is one of the few non-disposable backbone components of any business system.
Need help designing or updating a database for your business? We are experts in database design and would love to assist. Contact us today to learn more!
