If you are a developer reading this blog post you might as well know that databases are the foundation of any web or mobile application. When designing a database, it can be as simple as only having one table to store some configurations or as complex as having hundreds of tables related to each other.

So it’s only natural to think that starting with the right foot is an important step (ah!) for the success of your application.

If you’re looking into designing your first database but are not sure where to start, you’ve come to the right place. Welcome to Designing a Database 101.

Why Is a Database Important?

Just like any legitimate course, let’s begin with some theory: why are databases so important?

Well…

If your application handles information that must persist across the application scope you’ll need to store it somewhere. And that can be a database.

A database is basically the “place” where you store the data that is important for your app to work.

Let’s say you have a great sticker collection and want to sell some of those stickers. You’ll need an app for that. In order for the app to help your use case, you’ll need to store the inventory of stickers, the basic information of your customers so you know to whom and where to send those stickers and, of course, register the orders.

Database example - sticker store mobile app. 

How to Design Your First Database

For this exercise, let’s use the sticker shop use case because, who doesn't love stickers, amirite?

Creating Tables

The first thing we need to do is identify the main concepts of information. In this case:

  • Stickers
  • Customers
  • Orders

This gives you the main database tables you need to create to support your use case.

Database example tables. 

Defining Attributes

After that, you need to define which attributes are important and core for each of these concepts. Start by answering a simple question:

What information do I need for each of these concepts?

Can we have stickers without a name or description? Can we have customers without a delivery address? Do we need the date when an order is created?

Database example attributes.  

Establishing Relationships

Then, understand how these concepts relate with each other:

  • Can we have customers without products?
  • Can we have orders without customers?
  • Do we issue one order for each product or one order for all products for a given customer in a given time?

And a few more pertinent questions to ensure that our use case always has the correct information available.

This will give your data the proper structure to be consulted by avoiding duplicates. As an example, you would only need one line (or record) for Mary Jane’s information stored in the Customer table. Any other information about the requests to buy stickers from you would be stored in the Order and Order Line tables.

Database example relationships.

Got It! What Now?

No matter what kind of technology you’re using, designing a database should always follow this process:

  1. Identify the main concepts
  2. Define core attributes for each concept
  3. Establish relationships between them.

From here, you can add as many tables and attributes as you need for your specific use case.

If you want to dive deeper into the world of databases, you can watch my session at the OutSystems Developer Conference (OSDC), now available on-demand. In “Exploring Databases”, I cover additional topics like entity-relationship diagrams, primary and foreign keys, data modeling, indexes and constraints, CRUD and more!

You can also watch (or re-watch) all presentations that cover a wide range of topics, such as architecture, front-end, AI, cloud, and mobile.