You are here

Logical Database Design

19 January, 2016 - 12:35

As we did in THE “ORDER-TO-CASH” PROCESS: PART I, MARKETING AND SALES (M/S), 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 12.15, 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 THE “ORDER-TO-CASH” PROCESS: PART I, MARKETING AND SALES (M/S)  (see Figure 11.18). 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 12.16, parts (a) and (b), reproduces selected relations from Figure 11.19  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 12.15. 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.

media/image20.PNG
Figure 12.15 Entity Relationship (E-R) Diagram (Partial) for the RC Process
 
media/image21.PNG
Figure 12.16 Selected Relationship Tables (Partial ) for the RC Process 
 

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 12.5).

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.