This article is going to be interesting to you if one of these points applies:
Great, you are still reading! You might notice that spreadsheets are sometimes being used for things that they have not been made for. A few of those things could be project planning, todo lists, a complete CRM or product inventory. When making these things you will notice that it becomes too complex for a spreadsheet. This mainly has to do with the fact that you can't make relations. Lets say you are making a project planning and want to know which team member is going to work is which task. You need to make a relation with the team member and the task. It becomes especially difficult if you also want to group these tasks in sprints or milestones and if you have customers that can have different projects. Imagine doing this with a spreadsheet, keeping everything clear and organised while collaborating with your team members. We also think this is very hard!
You are also limited in which types of data you can store. By default the spreadsheet recognizes which value you are adding to a cell. This could be a text, number, date or a formula. But what if you want to store images, files, checkboxes, bar codes, ratings or you just want to add a little bit of logic to a cell, for example where you can only choose from a set of choices or for example a number with 3 decimals after the dot or comma.
This is where a relational database comes in. The name already suggests, this is software made specifically for creating relations between things (team members, tasks, groups, sprints). These things are actually tables and can hold all kinds of information. The same type of table that you insert in your text editor. The only difference is that every column only accepts a value of a predefined format. This might sound complicated, but is not. If that format is for example a price, a number with two decimal places after the dot or comma, then it is only possible to enter prices in the column. Anything else will be rounded or deemed invalid. A column could also for example also be a small peace of text indicating the name of team member.
If you are working with a spreadsheet you are probably making tables almost every time. The only difference is that with a spreadsheet you can enter any type of data in every cell. This makes your data unstructured and unpredictable. The reason why almost every website, app or other form of software uses a relational database to store all the information is that it works very well. The good news is that you don't need to be a software developer to build a relational database. There are online tools like Baserow and Airtable, which are spreadsheet and database hybrids, they run in your browser and you can use them without any technical experience. If you know how to work with a spreadsheet you can easily start with these tools as they will look familiar to you.
To give you an even better idea of how to use a no-code tool like Baserow or Airtable we going to cover every step you need to in order to build your own project management database. You might want to build something else, but this is just to get a grasp of the possibilities. After this tutorial you will be able to build anything you like. We are going to use Baserow instead of Airtable because this is open source software and that will come with lots of advantages in the future. Read more about the differences between Baserow and Airtable here. Even though the interface looks a little bit different the principles of a relational database are the same, so you should also be able to follow the steps also with Airtable because there are lots of similarities.
In order to start using Baserow you need to create an account on their website at https://baserow.io. You can do that by clicking on the "Register" button in the top right corner. Here you will see a small form that you need to fill out. After doing so you will immediately be presented with all your databases. Of course you have not yet created one, but for example purposes they have automatically created "YOUR_NAME's company" database. You can click on it which will bring you to the first, customers, table. If you click on one of the cells you will see that it works similar to a spreadsheet. Try to edit some data, it will feel familiar.
Luckily for us we already have two tables that fit our needs perfectly, customers and projects. But for our use case we are going to be needing 3 additional tables, one to store our team members in, one for the tasks and of course the milestones. You can add those tables by clicking on the "+ Create table" button in the left sidebar. Once have created them you can open the "Team members" by clicking on it in the left sidebar. You will notice that there are 3 columns (or fields as they are called in Baserow). Only one of them will be useful for this use case which is the name. The other two can be deleted by clicking on the small arrow pointing downwards next to the field name and then on the "Delete field" button. For a team member we might want to store a job title. We can do that by creating another field via the "+" button next to all the fields.
If all of that worked out you will have a basic idea of how to manage tables and columns/fields. In line with building our project management database we are going to work on the task part, please switch to that table and remove the "Notes" and "Active" fields. You should only have the first "Name" field left. At the beginning of this guide we talked about making relations between tables. There is a field that allows you to make these relations, it is called the "Link to table" field. If you choose that field type while creating a field, a second option appears where you can choose another table. Because a task belongs to a certain project we are going to make a relation with the "Projects table.". Once you have done that you will notice that if you click on a newly created cell you see a small plus button. By clicking on it you can choose a row from the "Projects" table and by doing so you are making a relation. You have now linked two rows to each other. It appears not to be making sense because you have for example linked the row "Tesla" to the row "Tesla" of the projects table, but once you rename the "Tesla" row in the tasks table to something like "Create self driving software" it will immediately start to make sense.
If you open up the projects table you see that another field named "Tasks" has automatically been created. This is basically the reversed field of the newly created "Project" link row table field. Here you can see that the project Tesla has the "Create self driving software" task as relation.
In order to finish the project management database several fields have to be created. You now have a basic understanding of how a relational database works so you can probably finish it by yourself. Below are 5 screenshots listed of how each table could look.
This month's update includes, along with some great contributions from the community, the much requested importing and exposing data publicly via a REST API features.