how to design a database for an application

Have you ever wondered how to design a database for an application? In this blog post, we’ll walk you through the steps to do just that.

First thing’s first: what is a database? A database is simply a collection of data in which each piece of information has a name and an associated value. You can think of it like a spreadsheet that stores your data in rows and columns, with each row representing an individual item, or record. Each record has its own unique identifier (or key), which lets you find it easily later on.

The most important part of designing a database is choosing what kind of data you want to store in it. If you’re building an app that tracks customers’ orders, maybe you want to store their names, email addresses, phone numbers and shipping addresses as separate fields within each customer’s record. This way each customer will have its own unique set of data—and if one day you decide to add another field for credit card information or product preferences, all those existing customers won’t be affected by the change!

how to design a database for an application

A properly designed database provides you with access to up-to-date, accurate information. Because a correct design is essential to achieving your goals in working with a database, investing the time required to learn the principles of good design makes sense. In the end, you are much more likely to end up with a database that meets your needs and can easily accommodate change.

This article provides guidelines for planning a desktop database. You will learn how to decide what information you need, how to divide that information into the appropriate tables and columns, and how those tables relate to each other. You should read this article before you create your first desktop database.

What is good database design?

Certain principles guide the database design process. The first principle is that duplicate information (also called redundant data) is bad, because it wastes space and increases the likelihood of errors and inconsistencies. The second principle is that the correctness and completeness of information is important. If your database contains incorrect information, any reports that pull information from the database will also contain incorrect information. As a result, any decisions you make that are based on those reports will then be misinformed.

A good database design is, therefore, one that:

  • Divides your information into subject-based tables to reduce redundant data.
  • Provides Access with the information it requires to join the information in the tables together as needed.
  • Helps support and ensure the accuracy and integrity of your information.
  • Accommodates your data processing and reporting needs.

The design process consists of the following steps:

  1. Determine the purpose of your database. …
  2. Find and organize the information required. …
  3. Divide the information into tables. …
  4. Turn information items into columns. …
  5. Specify primary keys. …
  6. Set up the table relationships. …
  7. Refine your design. …
  8. Apply the normalization rules.

database design tutorial

Databases are at the heart of every web application. Their design, or schema, is literally the blueprint for how all information is stored, updated, and accessed. However learning about databases is difficult. There are many long, complicated books on database theory, but few simple explanations for beginners.

This is a tutorial on database design for beginners. It is the database primer I wished existed when I was starting out.

What is a Database?

A database is a program that allows for the storage and retrieval of data. Broadly speaking there are two types of databases: relational and non-relational.

Relational databases, which are far more common, consist of tables which are structured in columns and rows similar to an Excel spreadsheet. Popular database options including MySQL, PostgreSQL, Microsoft Access, Microsoft SQL, and Oracle are relational. They use SQL (Structured Query Language), which is a programming language just for managing data in a relational database.

Non-relational databases, by contrast, do not have a strict column and row schema. They are viewed by proponents as “more flexible” than their relational counterparts and are increasingly used by organizations like Facebook, Google, and the NSA for big data situations.

This tutorial will cover relational databases. If you’re new to databases, this is the easiest place to start.

Tables and primary keys

A database is made up of (often multiple) tables. A simple table, let’s call it customers, would look like this:

Customers Table

The columns are “customer_id”, “username”, “email”, “password,” and “birthday.” Each row is known as a record. We could eventually add hundreds or even millions of records as our number of users grew, but the defined columns would stay the same.

The “id” column is what’s known as a primary key: a unique and non-null number that refers to each record. Every record/row in a relational table has a primary key.

To understand why, let’s imagine that the user “william” wants to change his “username” to “bill.” How do we know which “password” and “birthday” to associate with this user? Without a primary key, we don’t.

Tables also become much, much more complicated over time. A primary key gives us a unique way to identify and manage each record.

Data Redundancy and Input Constraints

A key tenet of good database design is that each data item, for example a username, should only be stored once, in one location. This avoids have to maintain and update multiple locations, which is very error-prone.

A second tenet is to use input constraints to increase the reliability of your data. For example, in our “email” column we know that a proper email record should contain the “@“ symbol, so we can say that only data with the “@“ symbol can be entered in the email column. And in the birthday column, we want a date datatype here so only, say, birthdays entered with numbers will be accepted.

Linking tables with Foreign Keys

When designing a database, the key decisions are what data do you want to store and what relationship exists between them. Right now we just have a customers table, but let’s say we also want to store orders because we’re building an e-commerce website.

It’s tempting to think we can just add a column for orders to our existing table. But an order has multiple pieces of information we want to track. For example, at a minimum we’d want to know:

  • the order_id (primary key) so we can keep track of each order
  • the date and time the order was placed
  • the customer the order was placed by

This means we need a separate table for Orders. In order to associate a specific order with a specific customer, we can use a foreign key relationship through the customer_id field to link the two tables.

Orders Table

If we look at the Orders table schema we can see the first order was placed by customer_id 2, “john” in the Customers table. The next order, order_id 2, was made by “william.” And so on. Foreign keys let us link multiple database tables together.

Leave a Comment