觀摩AppSheet的第二個關聯式資料庫範例

在設計最基本的進銷存管理系統時，訂單的設計一定要用到關聯式資料庫的概念，如同在這篇「References Between Tables」的說明:

For example, an order capture app might contain the following tables:

1. Orders, with one row per order.
2. Order Details, with one row per line item.
3. Products, with one row per product being sold.
4. Customers, with one row per customer.

The order capture app might contain the following references:

1. Each Orders row will reference the Customers row of the customer who placed that order.
2. Each Order Details row will reference its parent Orders row.
3. Each Order Details row will reference its corresponding Products row.

For example, you may have a separate Order Details table for line items that reference an Order, but conceptually each entry should be considered "part of" an Order and shouldn't exist independently (put another way, the Order record "owns" the Order Details that reference it).

所以，我們看看這個「Order Capture」範例吧，複製到自己的帳號來觀摩。

下列看到自動產生的「Reverse References」: For each Ref you add, the system automatically adds a reverse reference in the opposite direction. For example, when you add the Ref from the Orders table to the Customers table, the system automatically adds a reverse reference from the Customers table to the Orders table.

Reverse references serve three purposes:

1. They allow you to navigate from one row to all of its related rows.
2. They allow the user interface to easily display a row along with all of its related rows in another table.
3. They allow aggregates to be computed like the count of a customer's orders or the total dollar value of a customer's orders.

下列要特別注意「Is a part of?」的設定: References can indicate not only that two tables are related, but that rows of one table should be owned by (or considered a part of) rows from another table. This is done by activating the IsAPartOf option in the Ref column structure. Typically, this should only be done in cases where rows containing the Ref column only make sense when associated with a row from the referenced table. For example, you may have a separate Order Details table for line items that reference an Order, but conceptually each entry should be considered "part of" an Order and shouldn't exist independently (put another way, the Order record "owns" the Order Details that reference it).

下列要特別注意「Dereference Expressions」的設定: You can use a Dereference expression to retrieve the value of a column in a referenced record. Do this by writing a Dereference expression in the form [Column Containing Reference].[Column in Referenced Table]. The Order Capture sample app includes a typical Dereference expression. The Order Details table uses the Dereference expression [Product Id].[Price] to retrieve the product's price from the Products table. In this expression, [Product Id] Is the name of the Reference column in the Order Details table that refers to the Products table. [Price] Is the name of the column in the Products table that contains the product's price. You can use Dereference expressions when computing values. For example, in the Order Capture app, there is a formula that multiplies [Product Id].[Price] with another column called [Quantity].