Table of Contents
Plan Your Tables
Updated
by Lesley
Scenario
In this article, we will walk through the process of planning your tables, a core part of every single Knack app. We will share tips and best practices along with answering common questions both beginners and Knack pros have about tables.
First, we’ll cover some basics about key Knack concepts related to tables. Then, we’ll walk through the planning process.
Requirements
If this your first time creating an app, you'll need to know some basics about adding tables, fields, pages, and views. You can start by reading our Builder Basics section.
Other good resources can be found in our designing the database and building pages sections of the knowledge base.
There are other articles in this series you can read before or after this article:
Steps
Understand Tables and User Roles
Tables represent common groupings of information- your data. Each table should only represent a single type of information- it contains properties that identify a single type of item.
For example, if we want to track events we will need to make an Events table. That table would group together data that describe your events - event name, date, location, and description. Similarly, a Product table would group data that describes your products - product name, description, dimensions, and price.
User roles are common groupings of data just like tables, but with special permissions so users can login! User roles are fundamental for enforcing access & creating permissions in your Live App, allowing you to show each user data specifically tailored to their role.
Roles represent types of users who will be accessing your app, like Employees and Managers. With roles, you can set up pages where each Employee who logs in only sees their own assessments, whereas Managers log in to see all the assessments of their assigned Employees.
Let’s walk through an example to see the difference between user roles and tables. In a customer portal app, we may want to track invoices and customers. Data like customer address or phone number, while it may be important to display on an invoice, does not identify the invoice itself. That data identifies the customer. So we will split these two different types of data into two tables.
If a table for invoices contains both order data and customer data, it no longer represents a single type of information. It also means you now have to maintain customer data in two places, making your data management more cumbersome and prone to inaccuracies. This article will help you learn how to avoid this!
You can use connections to associate customer data with each invoice. That way, each table can contain only one grouping of information - fields that identify that table. Keep reading for more info on connections.
Later, we’ll explore the difference between tables and user roles in greater detail.
Understand Fields
Fields are the individual pieces of data that make up an table.
Each field is specially formatted based on what kind of data they contain. For example, address fields contain sub-fields for street address, city, state, and country. File fields allow you to upload or link to documents.
Each table is a collection of fields that identify the data within. For example, our Customers user role has fields for the customer’s name, phone number, and address in addition to the default user role fields (password, role, status).
Connection fields are a special type of field created when we connect two tables. A connection field creates a relationship between two tables and allows us to share and associate data between them. After we connect Invoices to Customers, a connection field for Customers will appear in the Invoices table.
This connection field will display the Customers records in a searchable dropdown. By default, it display values from the first field in the Customers table, but you can customize this setting. It’s called the display field and you can edit it from each table’s settings.
See our guides About Fields and Field Types for more information.
Understand Records
Records are your actual data. If you are familiar with spreadsheets, you can think of a record like a single row. Each column is a field, and the whole sheet is your table.
In our Customers user role, we will have a record for each individual customer. The record is where the actual data is stored.
Understand Connections
Connections are represented as a special field type. Connection fields allow you to create relationships between table storing different data types within your app.
If you have used other database solutions, you may have used primary and foreign keys, links, or joins to create relationships.
With connections, you can:
- View related data: You can create relationships between your data so that when you view information about a company, you can also view information about all of the contacts that are associated with that company as well.
- Ensure users only have access to their own data: Limit the data that users see on a page so that they only see the data that is relevant to themselves. This way, customers only see the orders they have created once they login.
- Run calculations and visually summarize your data: Create reports that show the total sales made in a given month and even how much each salesperson contributed to that total.
In a customer portal app, we will connect our Invoices table to our Customers user role so each invoice in our database is linked to the customer it belongs to.
The connection field in each Invoices record only displays one field from the connected Customers record (the name field): however, because they’re connected we can access all the data from the connected record in our Live App.
For example, we can create an invoice page in our Live App that displays the invoice date, number, and total, along with data from the connected customer - name, address, phone number, and more. You can learn more about this in the Optional Functionality section of this guide.
See our guide Connect Related Data for more examples of how connections are leveraged to create powerful Knack apps.
Think About Your App
This is a good point to start writing down the objectives of your app:
- Who will be using it?
- What will they need to access?
- Do I have different groups of users with different access levels?
- What kind of data will my users be entering in my app? What kind of data do I want to share with them?
- Do I need any special formatting or field types for my data?
See our guide on Users & Access for ideas on what you can do with users in Knack. Our Workflow and Reporting guides also provide many examples of what your users can do in your app.
Use the guides above and your own project idea to start creating your app plan. You’ll use this plan to determine what tables and user roles you will need.
Map Out Your Fields, Tables, and User Roles
Now that you understand the basics and know what you want your application to do, you are ready to map out your data.
Let’s say we want to create a project management application. In this app, we need to track a few things:
- Details about the project, including goals, project manager, client, and budget.
- The time spent on and expenses incurred for each project for cost analysis and billing.
- Multiple tasks and milestones for each project, with different deadlines and people assigned to each item.
- Details about the client and contacts at each company.
There are also a few different groups of people who need to access the app:
- Admins need access to manage the client database by adding and editing client records. They also add new projects and assign a project manager.
- Project managers need to access their own projects, create and assign tasks, view and edit project detail. They also need to manage costs related to their projects.
- Employees need to view the tasks they are assigned to and update those tasks. They also need to track how much time they spent on their assigned tasks.
This is a very basic app outline that will allow us to start mapping out our tables and user roles. Now, let’s start splitting this up into tables and user roles.
Tables
First, let’s start with our client data. We want to track information about the client company, with the ability to track multiple contacts per client. This means we are tracking two types of data here: company data and contact data.
By creating two tables - Clients and Contacts - we keep each type of data separate. With a connection from Contacts to Clients, we enable access to Client data from each Contact record.
Let’s map out our Client and Contacts tables and their fields:
This graphic shows our Clients and Contacts tables, with the connection between them represented by the arrow Client connection field in Contacts.
Now, we move on to our project data. Projects have a few different types of data: data that identifies the project itself (project name, client, project manager, description) and data that is related to work performed for a project (tasks, expenses, milestones, and more).
We’ll need to break these data types out into tables in order to achieve the desired functionality in terms of access, workflow, and reporting.
First, we’ll need a Projects table that represents core project data.
Then, we’ll need tables for the additional data we’re tracking related to a project:
- Tasks - These are the individual tasks that make up the project work. Each task is related to a specific milestone. Project managers assign employees to each task. We want to track the amount of time an employee has spent on their assigned task.
- Milestones - These are the major project milestones. Each milestone has a deadline and a deliverable. Project managers determine the milestones. We want to track the tasks that contribute to a specific milestone, along with the amount of time that goes into each milestone.
- Hours - This is where employees track the time they spend on their assigned tasks.
- Costs - This is where the project manager tracks any expenses for materials associated with their projects.
You’ll have to connect these tables to ensure all data is tracked to the right project. We won’t cover how to connect them in this article. To learn more about planning connections, read the articles linked above in Requirements.
Let’s map out our project-related tables and their fields:
The graphic above shows the project related tables with their key fields. The connection fields are indicated with a connection icon.
Besides keeping our data easier to manage, using separate tables for these different data types also opens up some powerful app functionality:
- Automatically calculate the time spent on each task in real-time as new Hours records are added.
- Automatically calculate the total costs for a project (labor and materials) in real-time as new Hours and Costs records are added.
- Assign different Tasks for a project to different employees, allowing each employee to view and edit only their assigned tasks.
User Roles
Many times, the necessary user roles emerge during the process of app planning. When you are thinking about who will be accessing your app and what they need to do, you will see groups based on function.
In our plan above, we identified three groups of people who would be using the app - Admins, Project Managers, and Employees.
Let’s map out our user roles and their fields:
Each of these groups needs to be able to complete different tasks and have access to read, write, and edit different records. When you get to building your Live App, you will create different pages for each of these roles. Each page will contain views that allow the users in that page’s role to complete their different tasks.
Add Your Tables, User Roles, and Fields
Finally, it’s time to start building!
In this step, you’ll take all your planning from the previous steps to add your tables, user roles, and fields to your app.
Use the green “+” button in the Data section of the Builder to add your tables and user roles.
If you are adding user roles for the first time, you will need to activate them first. For instructions on activating user roles, see this article.
To add your fields, go to your table in the Data section of the Builder and click the "Add Field" button in the top menu. This will open field options in the toolbox and allow you to find the field type you want to add to your table. Take a moment to explore the different options available.
Then, click on a field type to add it to your table. You can do some configuration right away, such as customizing the name of the field.
To learn more about adding and editing fields, see our guide About Fields.
Once your tables and user roles are in your app, you can add your connections. For help with determining how to connect your tables, see our companion guides:
Tables vs User Roles
In many apps, you’ll need user roles to allow users to login and access the data. In Knack, user roles are used to control page permissions and access to your app.
Let’s say we are building a customer portal. Should we make a customers table or user role?
The answer to this depends on your business. In a business-to-consumer (B2C) scenario, your customers are individuals. In a business-to-business (B2B) scenario, your customers are companies, each with individuals who work there.
Ask yourself:
- What kind of data am I working with?
- Who needs to login?
In a B2C scenario, the customer name, address, phone number, total spend, etc are all data points that identify an individual - a single type of data. This individual needs to log in to view their orders, update their info, and more. That means we will create a user role for Customers.
In a B2B scenario, we have two types of data. We have the customer name, address, phone number, and total spend. All this data identifies a company. We also have contacts at that company. Each contact has their own identifying info - contact name, email address, phone number, job title.
In this scenario, we create an table for Customers and a user role for Contacts. Customers represent the company data - companies are not people, so they can’t log in. Contacts represent the data of the individuals who work at the Customer companies, who do need to log in.
Optional Functionality
Sharing Data Between Tables in Views
In many cases, you may want to include one table’s data in views for another table.
For example, in a customer portal, we have tables for Invoices and Customers. On our Invoice page in the Live App, we want to include Customer fields like the name and address.
You can combine fields from connected records in views, allowing you to display customer details on the invoice page itself.
See this guide for instructions: Combining Fields From Connected Records in Views
Sharing Data Between Tables on Records
There are some instances when you need to store the same field’s data in multiple tables to trigger rules or certain workflows.
For example, we may want to create a job portal where businesses can pay a membership fee to post jobs. If they don’t pay their fee, the jobs are not displayed in the job seeker pages.
If you create a status field on both the Businesses and Job Listings tables, you will have to maintain the current status of a business in multiple places - the Business record and each connected Job Listings record. If a business doesn’t pay their fees and becomes inactive, you’d have to manually update all of that business’ job listings.
That’s a lot of extra work. We can automate this by using a text formula or equation in the Job Listings table that automatically pulls in the status value from the connected Business.
That way, the status field, which identifies the Businesses table, lives on Businesses table only.
With the text formula, the status of the connected business is automatically up-to-date at all times on each connected Job Listings record without you having to do any extra work.
Then, we can set up source filters in our Live App to only display Job Listings records where the status of the corresponding business is active.
