Welcome to the module on understanding data. In this module, we discuss how you can use data elements with AppSheet capabilities to add functionality to your app. We will first discuss some fundamental concepts on working with data entities and the use of keys in tables. We review the various data types that are supported by AppSheet and how we can use formulas and expressions to modify the behavior of your app. We also discuss the use of slices to filter the data that app uses work with in the app and how we can use AppSheet constraints to implement data validation in your app. Let's start with data entities. AppSheet is a flexible app development platform that enables you to create apps from existing on new data. In this lesson, we discuss some of the basic concepts about data entities to help you organize data and make it easier to manage and use with your apps. We'll first discuss some basic concepts on how data is organized and stored in tables that are used by our app. Data is made up of records. A record is stored as a single row in your spreadsheet or database. An individual record may describe a customer, product, order, or other entity. For example, this record provides information about a product, such as its product ID, what category the product belongs in, a link to a visual representation of the product, and its price. You store each type of record or entity in its own table in the data source. A table is a collection of records of the same type or entity. For example, a customers table will store records of each customer and a products table, as shown here, will store records of each product. Think of tables as entities that simulate real things like people, objects, or locations. A column is an attribute of a record in the table. Each row in the table will have its own values for each column. For example, in the products table, each row has different values for some of the shared set of columns in the table: product ID, product name, product image, and price. As an app creator, you either use existing data tables or create new ones. As the app is used, data in the table undergoes changes. Rows in the table may have some of the column values updated, entire rows may be deleted, or new rows may be added. You also will typically use multiple tables in an app, one for each entity that is managed by the app. When designing the data for your app, you generally follow these steps. First, determine what tables to use. Next, define the set of columns that will store details for each record in each table. Then determine the key column that will provide a unique identity for each row within the table. Finally, determine the relationships between the tables. Let's review these steps in detail. Determining the set of tables that make up your app is the first step in designing your app and its data. The app's functionality will typically drive the design and structure of the data required by the app and determine the set of tables that are needed to implement that functionality. For example, an inventory management app will require data related to products, suppliers, shipping, and other information. After you determine the set of tables that will be used by the app, you then define the set of columns that will store details for each record in each table. When defining columns, it is important to avoid including multiple columns in the same table that will contain values which are repeated across many rows in the table. You should consider putting those columns in their own separate table and creating a relationship between the two tables. For example, a products table that contains a row for each product and its information should not also contain product category information because the product category information will be repeated for all products that belong in the same category. Instead, you should have a separate table that stores information about each product category. You then create a relationship or a reference from a product in the products table to its category in the product category table. Another design principle when defining your columns is to not store a list of values in a single column for any given row in the table. Consider how you would store a list of order numbers for a given customer. Because you should not store a list of the order numbers in a single column in the customers table, for each customer row, you may initially think of creating multiple columns to store the order numbers one in each column. This design, however, limits the number of orders a given customer could place via the app. Instead, you would create a separate orders table that contains information about each order that is placed through the app. In this table, you would have a customer ID column to indicate which customer a given order belongs to. In effect, you create a relationship or reference from the orders table to the customers table. With this design, a customer can place multiple orders that are represented by rows in the orders table. After you determine the set of columns in the table, you need to determine which column will contain the key that will provide each row with the unique identity within the table. It is important for the app to uniquely identify each row in the table. To do this, you identify one of the columns in your table as the primary key column that will contain a unique value for each row in the table. Using the products table as an example, the key column in this table is the product ID column. This column will contain a value that uniquely identifies each product in the table. In the case of products, it may make sense for the key to also be the product ID found on the product label or barcode. It is a general best practice to use an ID column in every table that is used by the app. We will discuss generating keys and unique ID values later in this module. Finally, you need to determine any relationships between the tables that are used in your app. To create a relationship between your tables when creating your app in AppSheet, use the reference. References are discussed in the Building No Code Apps with AppSheet Foundations course in the series. Storing related information in separate tables makes it easier to manage. For example, if you need to update information about a product category, you would only need to update a single row in the product category's table, instead of updating multiple rows if this information were co-located with products in the products table.
