Structuring your database

What a database is

Every app needs somewhere to keep its information. In Adalo that place is the database, and the easiest way to picture it is a set of spreadsheets. Like an Excel sheet, a database holds the data your app shows to people and the data those people type in.

Four words describe how it all fits together, and you will use them in every screen you build:

• Collection: one spreadsheet, holding one kind of thing. A Trips collection, a Users collection.
• Property: a column in that spreadsheet. For a trip that might be its title, its start date, its cost.
• Record: a single row. One actual trip, with real values filled in.
• Relationship: a link between two collections, so a trip can point to the person who leads it without copying that person's details into every trip.

Get these four right and the rest of the app has something solid to stand on. Get them tangled and every screen you build later fights you.

The Users collection and property types

Open the Database tab on the left of the Adalo editor and you will already see one collection there: Users. Adalo provides it for you, and it is the one collection you cannot delete. You can add properties to it and rename things, but it stays. That makes sense, since almost every app needs to know who is logged in.

When you add a property, Adalo asks what type it is. A few common types cover most of what a beginner app needs:

• Text for names, titles and descriptions. The first property Adalo gives a new collection is a Text property called Name.
• Date for anything that happens on a day, such as when a trip starts.
• Relationship for a link to another collection, which is where the four ideas above come together.

Choosing the right type matters, because it decides what the app can do with the value later. A Date can be sorted and compared; a piece of text cannot be treated as a day on the calendar.

Building the Trips database

Let us build a real one. We are making a Trips app, where each trip has a title, a start date and a leader. Follow these steps in order.

1. Open the Database tab on the left menu of the Adalo editor. You will see the Users collection already there.
2. Click + Add Collection, name it Trips, and click Add. Adalo creates the collection with one starter property called Name, because a collection must always have at least one property.
3. Click that starter property and rename it to Title. This is the trip's name, for example "Weekend in Theth".
4. In the Trips collection, click + Add Property, choose Date, name it Trip Start Date, and click Save.
5. Add the leader. Every trip needs someone in charge, but that person is already a User, so we do not copy their details into the trip. Click + Add Property again, choose Relationship, then choose Users. Adalo now creates a link, and in fact two properties: one on the Trips side and one on the Users side.
6. Adalo asks what kind of relationship this is. A person can lead many trips, but each trip has only one leader, so pick the one-to-many option, then click Done.
7. Name both sides so you are not confused later. On the Users collection, rename the new property Trips Led. On the Trips collection, rename it Leader. Click a collection, then its relationship property, then fill in the field.

At this point the shape of the database is done: a trip has a title, a start date and a leader. What it does not have yet is any actual trips.

8. Add a record by hand. Click the Trips collection, then the Records button, then + Add Trip at the top right. Fill in the Title and Trip Start Date and click Save. You will notice the Leader dropdown is empty, because there are no users yet. Add a couple of users the same way, then come back and link them as leaders.

One-to-many and many-to-many

The leader link you just made is one-to-many: one leader, many trips led. That is the everyday shape, and you will reach for it constantly.

The first assignment adds a second, different link. A trip should also have members, people who join it. Here one trip has many members, and one person can join many trips. That is a many-to-many relationship, a "Join Trip" link between Users and Trips.

The way to decide which shape you need is to say the relationship out loud from both directions:

• "One leader leads many trips; one trip has one leader." One side is single, the other is many, so it is one-to-many.
• "One trip has many members; one member joins many trips." Both sides are many, so it is many-to-many.

Say both sentences before you click, and the right option in Adalo becomes obvious.

Learn more: rules worth knowing

A few rules of the Adalo database save you real trouble once your app grows:

• There is no true one-to-one. If two things must pair up exactly, such as one host to one single event, use a one-to-many relationship and simply ignore the "many" side. It is rare, but it does come up.
• The first property is the label. Adalo uses a record's first property as its name everywhere else in the editor, so put something unique and readable there, like the trip title, and never leave it blank.
• CSV imports match on the label. If you upload records from a spreadsheet, a relationship only imports when its value matches the label of the related collection, so clean, unique labels matter.
• Name both sides of every relationship. A relationship creates a property in each collection. Name them both, straight away, or in a month you will be staring at two mystery columns wondering what links to what.
• Empty is allowed. A property can be left blank or unused. That is fine while you are still building.

Prefer to see it explained? Here is the recorded Code for Albania lecture on this topic.

Check yourself

1. In Adalo's four ideas, what is the difference between a collection, a property and a record? Give an example of each from the Trips app.
2. The Trips app links a trip to its leader, and also to its members. One of those is one-to-many and the other is many-to-many. Which is which, and how would you say each one out loud to be sure?
3. Why does Adalo insist that the first property of a collection is filled in with something unique, and what breaks if you leave it blank?