As we did in ***Chapter 10, we now look at how RC data would be structured in a database. To keep the discussion simple, we will look only at two basic economic events as they relate to this process—sales invoicing and cash receipts. We will not cover adjustments resulting from sales returns, bad debt write-offs, and estimated doubtful accounts. First, in ***Figure 11.13, let’s look at an E-R diagram of the invoicing and cash receipts events.
The SHIPMENT, CUSTOMER, and SALES INVOICE entities should look familiar from the E-R diagram in ***Chapter 10 (see ***Figure 10.13 on page 362). To those three, we have added a CASH RECEIPT entity. In this section, we will examine the relationships among SALES INVOICE, CUSTOMER, and CASH RECEIPT entities. Let’s next translate the E-R diagram into relational tables.
***Figure 11.14, parts (a) and (b), reproduces selected relations from ***Figure 10.14 (see page 363) in order to emphasize the connections (linkages) among relations. These selected relations also remind us that before invoicing a customer, we first have accepted a customer’s sales order, picked the goods, and shipped the goods to the customer. Part (c) shows the new relations depicted in the E-R diagram in ***Figure 11.13. To simplify the tables, we have assumed that each inventory line item picked and shipped is billed at a single unit sales price from the INVENTORY_ITEM table.
***
Further, SALES_INVOICES (part b) ignores freight, sales taxes, or other items that might be billed to a customer. By using the SALES_INVOICES relation in part (b) and extracting other data, as needed, from other relations, consider how you would prepare the invoice record shown earlier in the chapter (see ***Figure 11.5, page 384).
The CASH_RECEIPTS and CASH_RECEIPT pays for SALES_INVOICE tables in part (c) substitute for the cash receipts data and remittance advice data discussed in the preceding section. For simplicity, we ignore customer cash discounts in the tables shown. First, note that Cust_No in CASH_RECEIPTS allows us to associate cash receipts with particular customers for the purpose of monitoring customer accounts and assessing any needed bad debt adjustments. In addition, Invoice_No in CASH_RECEIPT pays for SALES INVOICE can be used to apply collections against specific open invoices. Finally, the linkages among CASH_RECEIPTS, CASH_RECEIPT pays for SALES_INVOICE, SALES_INVOICES, and CUSTOMERS can be used to determine customer accounts receivable balances at any moment in time.
- 44 reads
