🗃️Database

Creating logically organized databases is key to building your app. You'll learn what a database is, how to structure it, and some killer tips and tricks to help make your databases amazing!

Skill Level: Beginner

Before You Begin

• Make sure you have signed up for a free Adalo account and are signed in

• Be familiar with what a spreadsheet is (Excel or Google Sheets).

• You do not need a paid Adalo plan to use this feature

An Overview of the Database

Walkthrough

Simple

Let's walkthrough how to setup a simple database for a Trip app with different properties, relationships, records, and collections. Please click the In-Depth tab of this walkthrough for more information about these terms.

1.) To get started, click on the Database tab on the lefthand menu in the Adalo Builder

2.) You'll notice that a User's collection is provided for you by default - this collection cannot be deleted, only modified. Let's add another collection called Trips by clicking the +Add Collection button

3.) Name the new Collection "Trips" and click Add

4.) An initial property is provided for you called Name since a collection must always have at least one property, but you can change this property's type, title, and order within the other properties of the collection if there are any. Let's rename this property to "Title" by clicking on it, then filling in the input field.

5.) Next let's add a Date property to hold the date that a Trip begins. In the Trips collection, click on the + Add Property button and select Date.

6.) Rename the new date property something like "Trip Start Date" and click Save.

7.) Every trip needs a trip leader, but we don't want to have to add properties about a trips leader when we already have that information in the User's collection. Instead, let's add a Relationship property in the Trips collection. In the Trips collection, click + Add Property again except this time, choose Relationship, then choose Users. This creates a link between the Trips collection and the Users collection and as a result two properties are actually created - one in the Trips collection and one in the Users collection.

8.) Now Adalo is asking us what type of relationship this is. We can think of this in terms of what role the user plays in the relationship. In this case, a person might be the leader of multiple trips but each trip can only have one leader so we would choose Option 1 here, then click Done.

It is also possible to create this same relationship from the User's collection instead, only this time the logic is being viewed from the opposite collection so we would choose Option 2. Both ways are correct. You can think of this as looking through a pipe from two different ends - what you see from both ends is the same, only mirrored.

9.) Now that we have a Relationship property setup, we can't forget to rename these properties in both the User's collection and the Trips collection. Let's rename the User's collection one "Trips Led" and the Trips collection one "Leader". You can rename them by clicking on the collection, then the relationship property, then filling in the field.

10.) Now we have a basic database for Trips that have a title, start date, and a leader, but we don't actually have any Trips (records) in our database yet. We could build a form so that our users could create and add their own Trips, or we could also add records manually to the Database. To add them manually, just click on the Trips collection then click on the Records button.

11.) In the top right of the database popup, click on the + Add Trip button. Some inputs will appear for the properties we added like Title and Trip Start Date. Go ahead and fill this in how you like, then click Save. You'll notice that the dropdown for Leader does not contain any options because we don't have any users yet! Why don't you try adding some users manually as well, then link them to some trips?

You can also upload records to your database by Importing a CSV file.

12.) Now that we have a database setup and some records inside it, we can use this data in Lists in our app. We can also create Forms for our users to create and update their own Trips in the database!

In-Depth

Before you start building your database, let's look at what a database actually is. A database is simply an organized collection of information that is accessed electronically. A database allows users to Create, Read, Update, and Delete information that is stored on a computer. We call these functions CRUD for short.

The most common analogy used to describe databases is that "it's like a spreadsheet". While it is true that a spreadsheet can function as a database, the two are not quite the same. Let's take a look a diagram to see some differences.

Adalo Database Basics

In Adalo, anything related to the Database is marked by a magical Red color like you see in the iconic big plus sign . You can access the Adalo Database by clicking on the red Database button in the Adalo Builder:

Upon opening the Database for your app, you will see the first and most important component of the Adalo Database - Collections.

1.) Collections

Collections divide the database into tables that correspond to different types of data. You can think of these as the "nouns" in your app such as Restaurants, Trips, Users, Orders, Menus, Activities, etc. In the same way that you might have a table of Users and a table of Events, you would have a Users Collection and an Events Collection. By default, every app starts with a Users collection and you get to add the rest!

It's often hard to determine what deserves its own Collection. To make it easier to determine your Collections, you can think about things that can be listed out, seen, or created by your users. Take, for instance, a social media app with Users and Posts where Users can "like" a post. It may be tempting to also create a Likes collection, but when we list Likes, we are really listing Users who "like" a specific post.

Some sample Collections

If you click on a Collection, it will drop down a list of more options for you to add and change called Properties.

2.) Properties

These properties define the characteristics of an item that might be in a Collection. In our User's Collection example, users may have a name, an email, a birthday, a profile photo, or even an eye color. All of these are considered properties of that user.

Properties within a Collection called Users

There are different types of properties that you can add as well that define the type of data that you put in. To add a property to a collection, click +Add Property

• Text - Commonly known as a "string", this can be comprised of text, numbers, or symbols

• Number - Commonly known as an "integer", this can only be whole numbers or decimals. It does support negative numbers and up to 15 digits. Can be used in Custom Formulas.

• True/False - Commonly known as a "boolean" value, true/false is visualized as a checkbox

• Date/Time - Allows you to store date and time properties to the database. They are stored as UNIX timestamps in the database, but shown to us as friendly dates in various formats. Can be used in Custom Formulas.

• Date - This property stores dates only and does not store time information. It's also stored as UNIX timestamps and shown to us in friendly formats. Can also be used in Custom Formulas.

• Image - This property stores images into the database. It also allows you to access the stored image's public URL link as well. The file size limit is 50MB.

• File - This property can hold any file type in the database. From PDFs and JPGs to Powerpoint and .MP4 files. The file size limit is 50MB.

• Relationship - This property lets us link two collections together. Read on for more information on this property type.

Different property types you can add to your Collections

Properties can be renamed simply by clicking on them and changing the input field. They can also be reorganized if there is more than one property by clicking and dragging them within the stack of properties.

A collection must always have at least one property. The Users collection has 4 properties that cannot be deleted, only renamed: Email, Password, Username, and Full Name.

3.) Relationships

Sometimes, instead of having to store hundreds of properties for a record, we create special types of Properties called Relationships (or links) between Collections to reduce the clutter in the database. In our Users and Events example, an Event may have an event host. But instead of creating properties in the Events collection to hold the host's name, email, and photo, we can create a relationship between the Users and Events collection. This allows us to lookup information about an Event's host from the Users collection without creating extra properties in the Events collection. Relationships also go both ways across collections - they are what is called "reciprocal". In the same way that User's may have Events they are hosting, Events will also have a host. There are 2 main types of relationships in Adalo that refer to the number of records that are being linked across Collections:

• One-to-Many: This means that one record has a relationship with multiple other records in a different collection. The logic of this manifests itself in two ways - one from the collection you are viewing and the other from the collection you are linking to. For example, a single user can host multiple events or multiple events can have a single host - both are true one-to-many descriptions of the relationship.

• Many-to-Many: This means that records from both collections can link to multiple records from another collection. For example, it's possible that events have multiple hosts and that hosts have multiple events.

In Adalo, anytime you setup Relationships between collections, you will be asked to define type of Relationship the Collections share. Again, these are really only two types (One-to-Many and Many-to-Many), but they are shown as 3 options depending on the usage.

When creating Relationships, it's often useful to think about what role each Collection plays in the Relationship. In the example above, we might want to create a relationship that defines that an Event can have only 1 Host. So instead of reading the options with the term "User", we can replace "User" with the word "Host" to get a better sense of which option to choose. In this case, it would be Option 1. Which one would you choose if we were creating a relationship between "Participants" and Events? Try it for yourself!

Properties, including Relationships, can also be visualized as columns in Adalo's database, by clicking on the Records for the Collection which will also display the Records within that collection.

4.) Records

Records somewhat describe themselves in that they hold (or record) information within a Collection. Records are visually similar to rows in a spreadsheet except that all the cells in the row are bound to each other. In our Users example, 1 user would be considered 1 record in the Collection.

Ideally, your app will contain forms and other methods of allowing users to create, update, and delete records from the database, but you can also add records manually by clicking on +Add _____ in the top right of the records view. You can also search your database and upload or download .CSV files (spreadsheets) to your Adalo collections using the buttons in the top right corner.

Video

Example

Now that you've got the basics of how to build a database, be sure to check out the cloneable version of the app used in this walkthrough by clicking here.

Learn More

• Properties may be left completely empty or not used at all

• While One-to-One relationships do not exist in Adalo, sometimes it is necessary to adapt the One-to-Many relationship type for this purpose. These instances are rare, but do crop up from time to time. For example, if an event host can only be assigned one event at a time and the event can only have one host. The "Many" side of the relationship can be disregarded.

• The first property in a collection is used by Adalo as the records' label. Typically unique values work best here and it's best not to leave the first property blank.

• When uploading a CSV, relationships can only be imported if the value matches the label of the related collection. Typically numbers or IDs work best for this.

• When creating properties, be sure to properly name them so you don't get confused later on. This is particularly important for Relationships since it creates properties in both related collections. Don't forget to name the relationship property in the other collection too!

• When working with relationships please be aware that the first property on your "related" collection will be the one that will show up in your Relationship. If you are willing to pull another property from your "related" collection, you will have to make this property the first one in the Collection.

Related

Collection PermissionsImporting Records from a CSV FileLocation

Help

If you would like to further understand how to work with Relationships you would want to take our Create a Robust, Flexible Shopping Cart System course from The Adalo App Academy.

If you need additional help with this article, you can always ask in our community forum! Be sure to paste the link to this article in your post as well!

Do you have a tutorial or help doc request? Let us know!