What is data modelling?

Data modelling, in the context of Baserow, is the process of deciding what tables and fields you should create in your database to best represent your data.

Why does it matter?

When modelling your data structure incorrectly, you run the risk of duplicate data entries which can lead to unnecessary extra work, and risk of business errors if you forget to update every entry. It can also lead to empty cells which can reduce the usability and visibility of your data.

It is important to get your initial data structure right. Migrating your database to a new structure after you have already added a lot of rows to your tables is a difficult and time-consuming process, it’s best to get it right the first time.

What are the best practices?

When modelling data in Baserow you can generally follow the best practices of data modelling for conventional relational databases. After all, Baserow is a no-code database, so general database best practices tend to apply.

General Guidelines to Follow

There are some exceptions but in general, you can follow these guidelines to keep your data structured well.

• Never store multiple values in one cell unless it’s a field type that specifically supports that
  • Exceptions: Link to table, Lookup, Multiple Select, File (…)
  • Those are exceptions because they are actually represented as separate tables in the underlying data structure
• Never manually add the same data twice
  • You generally want to add another table that you can reference if you are copying and pasting data
  • It also provides you with a single source of truth that can be updated once and applied to your entire database
  • You can reference the single source of truth in Baserow using Link to table Fields
• Avoid having empty cells
  • Empty cells can indicate that the data stored in the row doesn’t always require this column
  • If you have many empty cells, you should probably start splitting up the data into different tables
  • This is a softer rule than the others since there are enough legitimate cases for having some empty cells

Example

Let’s say you are storing your employees’ data in a Baserow Database and you are keeping track of their workplace departments and clients. You might have a table that looks like the one below.

Employee Table

The Employee Table holds all the information you need, but you can see that the “Clients” and “Department Address” columns both hold multiple pieces of information in a single cell.

If we want to add another person working for the sales department to this table, we would need to copy and paste all the department information into that new row. And if we would want to ever change the address of the sales department, we would need to make that change for every row as well.

Improving the Employee Table

Instead, what we should do, is we should give “Clients” and “Departments” their own table, so we can centralize any data specific to a client or a department.

Department Table

Having a separate table for departments allows us to only ever define the address of a specific department once and gives us the ability to change the address if we ever have to in one centralized part of your database (single source of truth).

Splitting the address information into multiple columns allows us to apply filters easier, for example, if we would want to see all the departments in city “X” we could filter on the “City” column.

Clients Table

Giving the clients their own table allows us to add extra information to each client, such as the number of employees they have, for example. It also allows us to have one central place where we can change the name of a client for example and have the change automatically applied to the rest of the database.

Employee Table

The employee table now just has to link to the “Clients” and “Departments” tables.

Access additional data now hidden in another table

Employee Table

If we need additional information in this table, for example, the city of the department, we could simply add a lookup field which gives us extra information about the department.

If you want to learn more about Baserow, go to baserow.io and sign up for free now!

What’s your story? Tell us how you use Baserow