How to Design Your First Database

If you work at storing data for applications, websites, or anything else then you are probably familiar with databases. If you have ever needed to design a database, then you know how many details there are that you need to be aware of during the design process.

If you have never created your own database before, then we thought that this would be a good time to go over some basics about how you should go about doing it. 

The whole process can be a little intimidating if you are trying to create something that other people are going to rely on to get work done, so this will help you get it right.

Database Design Golden Rules

What makes a good database design? In database design, there are two golden rules: 

• Avoid duplicate information and redundant information: Anything that is duplicated or redundant is unnecessary and will therefore take up extra storage. We want to eliminate this as much as possible in a database for obvious reasons. 

These reasons include better storage management, better performance, and more efficient queries. If data is stored in multiple places, then the chances of that data being changed in only one place can lead to inaccuracies, which we cover in the second golden rule.

• Capture Accurate and Complete Data: There is an old saying in computation: garbage in, garbage out. If you are capturing information that is incomplete, or just plain incorrect, then you are not going to be able to recall that data for any meaningful purpose. Rather systematize your tables and allow the database to enter in dates, ID numbers, and anything else that could be incorrectly entered. 

Automating these data entries leads to far more accurate data storage. These are called constraints, which we explain in more detail further on in the article.

Following these rules will make your database perform well and prevent problems in the future, which is all we really want in a stable data storage medium.

A Database Design Example

Let’s say that we want to create a database for an application or web app that deals with ordering products. performs well, contains the correct data, and doesn’t take up too much space.

Duplicating data means repeating the same entry multiple times, using up space that we really didn’t need to use. Here is an example:

This example shows us that we have duplicated the Name, Address, Products, and Dates. That is way more data than we needed to store in this table. When it eventually grows to thousands of entries, the system will be very slow to query.

In order to track customers, their orders, and the products they ordered, we should create three separate tables rather than a single one, and link them together using primary keys and foreign keys like the below:

This way we can design our queries to get data from multiple tables to end up with an output that looks the same as our first example, but by storing far fewer data entries in the backend.

Related: How Kerberos Works in Windows Active Directory.

Primary Keys and Constraints

By putting constraints on columns, we can ensure the accuracy and completeness of our data. We can accomplish this by using constraints when designing our database.

• Check Constraint: This ensures a valid range of values in a table. If there is a mismatch, then data will not be entered, thus ensuring accuracy. 

• Foreign Key Constraint: This enforces relationships

Check constraints are measures put in place that ensure a valid range of values within a column is entered. A foreign key constraint enforces relationships between multiple tables.

In our example above, the primary key for the customer's table is the customer Id, for the products table is the product Id, for the orders table is the order Id, and for the order details table, it's both the order Id and the product Id.

Orders contain a foreign key because it references customer Id, which means that an order entry cannot be created unless there is a customer Id from the customers table.

The Database Design Process

Essentially the design process involves identifying everything you need to track in the database and how it will be structured.

From real-world objects, you can extract data points. For instance, you can extract the height, width, frame style, artist, and what the picture actually is by looking closely.

Then, create tables and columns for all the information you want to track in a database about that object. Then set up relationships between those tables.

In the final step of the design process, you need to normalize and refine your database design. Normalization requires five normal forms, and you can hit these golden rules with the third normal form. 

Here are the steps laid out in order:

• Determine the purpose of your database

• Discover and collect information

• Divide the entities into tables

• Turn data points into columns

• Identify the primary and foreign keys

• Normalize everything and keep refining

Wrapping Up: Designing Your First Database

There is nothing too difficult about designing and creating databases if you know where to start, and what to look for. We have covered the basics in this Intro to Databases course that you will need to create something usable, manageable, and scalable for storing your data. 

Once you realize how to segment the information that your application or website needs, then the process becomes a simple matter of repeating the steps until you end up with something that is well thought out and easy to write queries for.