Design Your Database

Behind each Knack app is an online database - this stores all the data that your app will use.

This article covers the following topics:

• How does Knack organize your data?

• How does Knack store this data?

• How does Knack use this data?

• Connecting Your Data

• Special Tables

  • Users

  • Payments

To start building your app, you need to first determine the data it will store and how it will interact. This process takes place in the Data section of the Knack Builder:

How does Knack organize your data?

In Knack, each piece of data you want to store and manage is called a field, and similar fields are grouped into tables. You can think of a table like a single sheet in a spreadsheet, and each field like a column in that spreadsheet.

For example, a typical "Products" table would have fields for name, part number, and product label. A typical "Orders" table would have fields for order total, date, and delivery status.

Each field can be a different type, like text, number, or multiple choice. These types define how Knack views your data and how the field is used in your app. For example, a file field is used to upload and download files, while a text field stores and displays simple text.

How does Knack store this data?

Your data defines what is stored in your database and what type of application you can build.

Once your tables are defined, you can add records in the Records section of the Builder. For example, here’s what stored records look like for a Products table:

How does Knack use this data?

In the Pages section of the Builder, you can build the Live App to display and manage the records stored in your app.

You’ll use the fields you added to build forms, grids, and other tools for viewing and managing these records.

For example, each field is available to add as a column in a grid view:

Connecting Your Data

What makes an app powerful? The ability to define relationships that connect your data. For example, instead of each order record existing in isolation, you can connect it to the product record used in that order. If that same product is used in a second order, you don’t need to keep adding the product details over and over; you connect the second order to the same product record that already exists from the first order.

Each table lists its connections in the right column. When you add a new connection it creates a special field that defines the connection:

Knack makes connections easy. You don’t need to worry about mapping fields or managing keys; you look up the record you want to connect to.

Here’s an example of an order form where you look up the product to connect the order to:

Knack uses connections in a lot of powerful ways. For example, using the connection between customers and orders, you could:

• Show all of the order records for a specific customer

• Add up all the order totals for each customer

• Run reports on each customer’s average order total each month

• Let each customer log in to view their order history

Special Tables

Knack has two special tables that add functionality on top of a normal table: Users and Payments. Both user accounts and payments can be used just like regular tables in your database.

They are listed separately to emphasize their special nature.

Users

User accounts are tables with special email and password fields that are used to manage user logins. You can require logins to access your Live App, and each user record would represent a user account that can log in.

You can create multiple user tables to represent different roles and permissions. For example, you may have customer users who can log in to one part of your app to view their order history and shipping manager users who log in to another part of your app to view pending orders and ship for delivery.

There are additional security settings available with user logins to help ensure the security of your Live App pages.