Relational Database Design: Know the Basics

Transcript:

Hi! Today, we want to speak to you about relational database design. Having the right approach when looking to effectively design a relational database is important to ensure that in the end all users can navigate through your database as quickly and efficiently as possible to access the information needed.

If you are considering creating a database, even just at the prototype level, we have some manageable tips to help you get started.

What is a Database?

A database is a way for you to store, filter, and sort through data – and can be incredibly helpful in the case that you need to track meaningful information. If you have some sort of device, for example, perhaps a temperature sensor, and you need to record the temperature sensor’s reading for a set frequency; perhaps it’s every 10 seconds, every minute, or every hour. A database is a great way to pull that data from an embedded device and store the data in a central location, even better when it’s done in an automated manner. Sometimes data can be input in a more manual fashion, for example: a clinician adding patient information into an Electronic Medical Record. However the information is added, whether it be automated or manual, having a good database building foundation is crucial so that users can easily access the information at hand.

Planning

If you wish to create a database, maybe to compliment a product you’re developing, it would be wise to sit down with your development team to plan out how your database needs to function.

Consider prototyping a database. Talk with your software development team and determine what makes the most sense for your database: either hosting it in the cloud or hosting it on an on-prem (or local) server.  Should you determine a cloud-based database best suits your needs, you can then dive deeper into determining which cloud service is most appropriate, for example Amazon Web Services, Microsoft Azure, or Google Cloud, to name a few. But, if you determine that your database would best be hosted locally, then you should continue talking with your software engineering team about the language and development framework that you could use. For example sql/my sql.

The initial planning phase should lead to a solid design.

Design

When designing your relational database, it is important to consider all of the information you want to collect and store in your database. The information that you want to collect and store in the database are called attributes. Essentially, attributes will then be broken up or grouped into tables, and these tables are known as entities. Once you know what attributes you want in your database, you can then organize and group them in a way that makes the most sense. By strategically organizing your attributes during the design phase, future users will be able to easily access and manipulate the data that they wish to obtain. Entities with relational attributes allow the database to feel organized so that user’s can more efficiently sort through the data.

Going back to the temperature sensor example mentioned earlier. Say you have more than 1 temperature sensor with information being recorded in the database. It’s important to know the relationship between the recorded attributes in the database and the physical sensor providing this information. During the design phase, the engineers may want to consider implementing keys – the keys connect certain attributes to the appropriate entity table or respective attribute when you are navigating through the database. Keys would essentially call out the necessary information that belongs to the temperature sensor in question. The relationship between the entities and their keys can be as complicated or basic as you would need them to be.

Another consideration when designing your database should surround data encryption. How do you plan to encrypt your data? We suggest ironing out those details early on so that the development team can plan accordingly. Two common encryption types are encryption in transit and encryption at rest. Again, going back to our earlier example of the temperature sensor – encrypting data as it’s being transmitted from the sensor to your database will protect the data, should it be compromised during the transmission. Another option, sometimes used simultaneously as encryption in transit, is encrypting the database’s data at rest, essentially protecting the data that is stored within the database from being compromised or hacked. How you plan to do this is truly dependent on your needs and what will best suit your use case, but it’s important to think about and discuss encryption with your team.

Additional Database Design Best Practices

We wanted to share a few additional database design best practices that we think you will find very helpful and, if nothing else, provide you with some reference points when going into planning.

The first is database normalization. Normalization consists of a set of steps to force engineers to go through the database and look for situations or characteristics to potentially simplify how it is going to be structured and how everything is linked. The normalization process may be done by creating tables and identifying and establishing relationships between these tables, ultimately reducing data redundancy to a minimum.

In addition to normalization is indexing. The indexing process structures the database in such a way that allows for more optimal data retrieval. Rather than combing through each row within the tables, you can create indexes that allow the user to quickly search through sorted lists and tables.

Lastly, we suggest making use of stored procedures. The purpose behind stored procedures is just as it sounds: stored procedures are prepared, or frequent searches, that are stored within the database to save time in searching rather than setting up the same search query over and over. This can be done via code and Structured Query Language, or SQL statements.

Conclusion

We hope that you will find these tips surrounding database design helpful as you start thinking about your database needs and how you will begin to structure your database in a way that achieves a favorable design for the end user. If you’d like to dive further into any of these concepts, please don’t hesitate to contact us. We are here to help and have many great resources that are well versed in database design and development. We look forward to being of service!