Database Design

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.

TypeName
RelationalMicrosoft SQL Server
RelationalMySQL\MariaDB
RelationalPostgreSQL
RelationalSQLite
NoSQL\DocumentMongoDB
NoSQL\GraphNeo4j
Time SeriesPrometheus & 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!

Put Stuff Where it Belongs

Some time ago I came across this while reading a book – SQL Server 2008 Step by Step (Mike Hotek, Microsoft Press) – and to date I think it’s the best description of normalization that I’ve ever seen… it just took me this long to get around to obtaining permission to post the excerpt!

I don’t have much further to say on it, other than many thanks to the O’Reilly permissions people, so without further ado…

Entire books have been written and multi-week courses taught about database design. In all of this material, you will find discussions of first, second, and third normal forms along with building logical and physical data models. You could spend significant amounts of time learning about metadata and data modeling tools. Lost in all of this material is the simple fact that tables have to be created to support an application and the people creating the tables have more important things to worry about than which normal form a database is in or if the remember to build a logical model and render a physical model from the logical model.

A database in the real world is not going to meet any theoretical designs, no matter how you try to force a square peg into a round hole.

Database design is actually a very simple process, once you stop over-thinking what you are doing. The process of designing a database can be summed up in one simple sentence: “Put stuff where it belongs.”

Boiling down these tens of thousands of pages of database design material into a single sentence will certainly have some people turning purple, so let’s investigate this simple assertion a little more closely.

If you were to design a database that will store customers, customer orders, products and the products that a customer ordered, the process of outlining a set of tables is very straightforward. Our customers can have a first name, last name and an address. We now have a table named Customer with three columns of data. However, if you want utilize the address to ship an order to, you will need to break the address into its component parts of a location, city, state or province, and a postal code. If you only allowed one address for a customer, the address information would go into the customer table. However, if you wanted to be able to store multiple addresses for a customer, you now need a second table that might be called CustomerAddress. If a customer is only allowed to place a single order, then the order information would go into the customer table. However, if a customer is allowed to place more than one order, you would want to split the orders into a separate table that might be called Order. If an order can be composed of more than one item, you would want to add a table that might be called OrderDetails to hold the multiple items for a given order. We could follow this logic through all of the pieces of data that you would want to store and in the end, you will have designed a database by applying one simple principle: “Put stuff where it belongs.”

Microsoft® SQL Server® 2008 Step by Step by Mike Hotek published by Microsoft Press, A Division of Microsoft Corporation Copyright © 2009 Mike Hotek. All rights reserved. Used with permission.