What is database design?

Database design (also known as a database schema) represents the structure of a database. It outlines the relationships and constraints between the entities in a database so you can see how they connect to each other.

Software developers and designers create database design diagrams to determine how best to store, manage, and access data. With a clear picture of all the components, they can also identify areas for improvement in existing schema. As a result, they can make changes to improve performance and meet user requirements.

Database diagrams vary in structure. The most common format is a type of flowchart, where the entities are represented as table structures. Arrows connect the entities to show how they relate to each other, which way the data flows, and what type of information moves between the entities.

We’ll look at some database structures in more detail.

When to use database design

There are certain situations in which database design principles come in handy. Here are some common scenarios:

To develop new software

Designing a database is a great way to outline a new software’s elements. It gives you a clear format through which to map all the entities, attributes, and relationships within the system. This means that you can visualize how the database will work and where you can make improvements before going live.

To map a complex program

If you have a particularly complex program, a database diagram can help you visualize its key components in a simple way. You can use tables, columns, keys, and relationships in the diagram to break down an intricate database system. This allows you to visualize the database’s basic structure.

To improve an existing database

Database design allows you to visualize the elements of an existing database. You can see how your database functions, how the entities relate to each other, and how information moves through the system. As a result, you can identify areas for improvement.

To align your team

Having a centralized database removes any confusion between employees. Everyone can see what the database includes, its structure, and how it stores information. There’s no room for interpretation, meaning that everyone’s on the same page.

To improve collaboration

A centralized database also makes it easier for teams to collaborate. There’s no need for any back and forth about how the database functions, as it’s all in one document in one location. This makes collaboration more efficient.

What are some types of database design?

Designing databases can be done in various ways, with different diagrams that can be used for different purposes. Here, we’ll outline three of the most common.

Entity relationship diagram

An entity relationship diagram (ERD) outlines how entities (also known as actors) relate to one another in a system. In an ERD, entities refer to a system’s people, objects, and concepts.

Designers and developers can use ERDs to simplify complex systems. They can see how different entities connect and overlap within the system, which makes it easier to understand how the system works and how to improve it.

Take a look at Miro’s Entity Relationship Diagram Template for an example of how an ERD can be set up.

Context diagram

A context diagram is a high-level diagram that outlines how external entities interact with your system. This system could be anything from a website or application to a platform or product. External entities vary but usually include customers, companies, and other pieces of software.

Context diagrams are common in the discovery stage of a new system. Business analysts and stakeholders use them to visualize the scope of a system and identify any errors or omissions before going live.

Take a look at Miro’s Context Diagram Template to see the structure of this type of diagram.

UML (unified modeling language) diagram

A UML diagram outlines the structure of a system. Software developers use it to review existing software, model new software, and identify areas of software development.

A UML diagram itself divides relationships and hierarchies into components and subcomponents. This makes it easier for developers to visualize the key components of a system, how they interact, and how to improve them.

Although UML diagrams are traditionally used by software developers, they can also be helpful in other areas of business, such as managing processes, projects, and workflows. They help teams structure their workflow, streamline their processes, and create process documentation.

Miro’s UML Diagram Template demonstrates the structure and core components of a UML diagram.

How to design a database

Now that we know what a database design is and when to use it, let’s take a look at the database design process.

Choose the right structure

The first step is to choose the right structure to map your database. This will put you in a better position to map your software or system accurately.

Let’s say you want to outline how external elements interact with your software, but you choose a UML diagram instead of a context diagram. You’ll likely struggle to get a clear picture of how external entities interact with your system because that’s not what a UML diagram is for.

This is why you need to be clear about what you need out of designing a diagram. In doing so, you’ll create a database diagram that represents the information you want to see.

You also need to choose which database model to use — logical or physical. Let’s look at these in more detail.

• A logical model is a top-level relational database design. It outlines the structure of the data elements and how they relate to each other. Business analysts use this model to develop a map of the data structures. An ERD is a good example of a logical model.

• A physical model provides more detail than a logical model. On top of the data elements, it describes database-specific information. Developers and database administrators use physical models to create a database.

So how do you know which model to use?

The answer is simple: think about what you want to achieve.

If you want to map an entire system with all the data, a physical model is your best bet. If you want to get a top-level overview of different data elements, a logical model is the right choice.

In some situations, you may choose to create both a logical model and a physical model. You’ll use the information gathered in your logical database design to inform your physical database design.

Consolidate the necessary data

After deciding which type of database design to use, the next step is to collect the data and information for the database.

This is where data consolidation comes into play.

Consolidating data involves sourcing the relevant data from your database management system (DMS). If you don’t have a DMS, you’ll simply review any data you have.

Here’s how the process works:

Confirm your database’s purpose

To determine what information your database needs, you first need to understand the purpose of your database. Is it simply to store information, or do you need your database to perform other actions? For example, do you need it to automatically delete redundant data? You need to be clear on this before you start consolidating your data. That way, you’ll know exactly what to look for.

Analyze existing data

Now that you know what your database is for, you can analyze your existing data. Also known as a data review, this process involves taking a look at all the data that you collect as a business. From here, you’ll be able to determine which types of data are relevant to your new diagram.

Identify relevant data

Having reviewed your existing data, you can now pinpoint the data points that are relevant to your diagram. For example, if your database stores customer information, you might incorporate your customers’ first and last names, customer IDs, email addresses, and contact numbers.

Create the database diagram

With a clear picture of all the data you need, you can incorporate all this information into your database design. You can use Miro's ER diagram tool to create a database diagram from scratch or a ready-to-use template.

A database design diagram uses separate tables to represent the data sets (although this can depend on the type of diagram you’re making). The top of the table usually outlines the data category, with the attributes that fall into that category listed below.

You can then use arrows to establish how the entities relate to each other and how data flows between them. Depending on the type of diagram you’re using, you can also add cardinalities.

Cardinalities outline the numerical attributes of the relationships between entities. In other words, they represent how much data flows between them.

Let’s use a customer database as an example. Here are some cardinalities you might find in this database design:

• One-to-one relationship. Where one customer can only buy one product at a time.

• One-to-many relationship. Where one customer can buy multiple products at the same time.

• Many-to-many relationship. Where multiple customers can buy multiple products at the same time.

Each cardinality (drawn as a connecting line on the diagram) has a small symbol that reflects these numerical values. The symbols change based on how much data moves between them.

Share the diagram with your team

When the final database design is finished, you can share the diagram with the relevant stakeholders. This gives them a chance to provide feedback, ask questions, or give their approval to sign the design off.

You can then make the necessary changes based on users’ feedback before the database design moves to the next phase — database creation.

Design your database with Miro

Database design is a useful tool for visualizing and managing your database’s structure. A well-designed database shows all the entities in a database, how they relate to each other, and where the system can be improved.

Use Miro’s free database design tool to create and share your next database diagram.