Foundational data architecture patterns - Making Sense of NoSQL - page 44

Foreign key

Primary key

Table: ORDER_ITEMS

Table: SALES_ORDER

ORDER_ID ITEM_ID PRICE

123 83924893 10.00

ORDER_ID ORDER_DATE SHIP_STATUS TOTAL

123
124

2012-07-11
2012-07-12

SHIPPED
BACKORDER

39.45
29.37

123
123

563344893
343978893

20.00
9.45

125 2012-07-13 SHIPPED 42.47 124 83924893 29.37

125
125

563344893
343978893

20.00
22.47

Figure 3.4 Join example using sales, orders, and line items—how relational databases
use an identifier column to join records together. All rows in the SALES_ORDER table on
the left contain a unique identifier under the column heading ORDER_ID. This number is
created when the row is added to the table and no two rows may have the same
ORDER_ID. When you add a new item to your order, you add a new row to the
ORDER_ITEMS table and “relate” it back to the ORDER_ID that the table is associated
with. This allows all the line items with an order to be joined with the main order when
creating a report.

In this figure there are two distinct tables: the main SALES_ORDER table on the left
and the individual ORDER_ITEMS table on the right. The SALES_ORDER table con-
tains one row for each order and has a unique identifier associated with it called the
primary key . The SALES_ORDER table summarizes all the items in the ORDER_ITEMS
table but contains no detailed information about each item. The ORDER_ITEMS table
contains one row for each item ordered and contains the order number, item ID, and
price. When you add new items to an order, the systems application must add a new
row in the ORDER_ITEMS table with the appropriate order ID and update the total in
the SALES_ORDER table.
When you want to run a report that lists all the information associated with an
order, including all the line items, you'd write a SQL report that joins the main
SALES_ORDER table with the ORDER_ITEMS table. You can do this by adding a WHERE
clause to the report that will select the items from the ORDER_ITEMS table that have
the same ORDER_ID . Figure 3.5 provides the SQL code required to perform this join
operation.
As you can see from this example, sales order and line-item information fit well
into a tabular structure since there's not much variability in this type of sales data.

SELECT * FROM SALES_ORDER, ORDER_ITEMS
WHERE SALES_ORDER.ORDER_ID = ORDER_ITEMS.ORDER_ID

Figure 3.5 SQL JOIN example—the query will return a new
table that has all of the information from both tables. The first
line selects the data, and the second line restricts the results
to include only those lines associated with the order.