Audience: Intermediate
Introduction
Developing a scalable application begins with an optimized data architecture. While a single “flat” table is simple to set up, it often leads to data redundancy and maintenance challenges as your app grows. By adopting a Relational Database model, you can link tables together and ensure data integrity.
What this tutorial covers:
-
The core differences between flat and relational data structures.
-
Essential terminology: Primary Keys, Foreign Keys, and References.
-
A step-by-step guide to implementing Ref columns and “Is a part of” logic.
-
What is Reverse Reference and How to use Dereference expressions.
1. Understanding Relational vs. Flat Data
In a Single Table (Flat) structure, all data points, even those that repeat are stored in one place. For example, an “Orders” sheet might repeat a customer’s full address every time they make a purchase. This leads to data redundancy and increased risk of entry errors.
In a Relational Database, data is categorized into logical tables (e.g., Customers, Orders, Products). These tables are connected through References, ensuring that each piece of information is stored exactly once. This architecture provides:
-
Data Integrity: Update a customer’s phone number in one place, and it reflects across all related orders.
-
Navigation: Seamlessly jump between a product and all orders that include it.
-
Efficiency: Smaller, focused tables improve app performance and sync speed.
2. Key Concepts: Keys and References
To build this in AppSheet, you must understand two primary components:
-
Primary Key (PK): A column in the Parent table (e.g., Customer Name) containing a unique value for every row.
-
Foreign Key (FK): A column in the Child table (e.g., Orders) that stores the Primary Key of the related Parent row (e.g., Customer Name).
3. Implementation: Creating References (The “Ref” Type)
A reference (Ref) is a connection between two tables. In AppSheet, a reference is established by adding a column in one table that stores the Key value of a row in another table.
Step-by-Step Configuration
-
Prepare your Data Source: Ensure your “Child” table (e.g., Orders) has a column intended to hold the unique ID (e.g., Customer Name) of the “Parent” table (e.g., Customers).
-
Navigate to Data Settings: In the AppSheet Editor, go to Data and select the table you wish to edit.
- Set the Type: Find the column you want to use as a link (e.g., Customer Name) and change its Type to Ref.
- In the Table editor (Data > Table Name (e.g., Orders)), click the pencil icon
of the Ref column to edit the column settings.
- Under the Type Details, select the Referenced Table Name (the Parent table) into the Source Table field.
4. Reverse References and Virtual Columns
When you create a Ref from Table A (e.g., Customers) to Table B (e.g., Orders), AppSheet automatically creates a Reverse Reference in Table B.
- How it works: AppSheet adds a Virtual Column to the Parent table using the REF_ROWS() function.
- The Benefit: This automatically creates an “Inline View.” When you open a Customer’s profile, you will see a list of all related Orders without any additional configuration. This allows for powerful “Parent-Child” navigation.
5. Expressing Ownership with “Is a Part of”
In some cases, a record in a child table should not exist independently of its parent. For example, Order Details (line items) are logically “part of” an Order.
Steps to enable “Is a Part of”
- Navigate to Data Settings: In the AppSheet Editor, go to Data and select the table you wish to edit.
- Set the Type: Find the column you want to use as a link (e.g., OrderID) and change its Type to Ref.
- In the Table editor (Data > Table Name (e.g., Order Details)), click the pencil icon of the Ref column to edit the column settings.
- Under the Type Details, toggling the “Is a Part of” setting.
By toggling the “Is a Part of” setting in the Ref column:
- Integrated Forms: You can add line items directly inside the Parent table’s form view.
- Cascade Deletes: If an Order is deleted, all related Order Details are automatically removed, keeping your database clean.
6. Utilizing Dereference Expressions
Once a relationship is established, you can retrieve information from a related row using “Dot notation” (Dereferencing).
Example: A reference is established between the Parent table (e.g., Products) and Child table (e.g., Order Details) through the Ref column (e.g., Product Id).
To display the Product’s price inside an Order Details record, you can use: [Product Id].[Price] in the App Formula field of a column (e.g., Total).
- In the Table editor (Data > Table Name), click the pencil icon of the column (e.g., Total) to edit the column settings.
- Under the Auto Compute section, enter the formula into the App formula field.
This eliminates the need to manually copy data between tables, ensuring your app remains lean and your data remains accurate.
For more information, Refer to the Support article or use the Order capture how-to sample application.