Refining the Business Model
From a data-model perspective, the simple retail-sale metamodel works. But from a business perspective, designating each item purchased as a separate sale might not be the method you want for recording your data. This approach is cumbersome; when you record your data by item purchased, you have to reconstruct each shopper's trip to the store by time- and resource-consuming sorts and data ordering. And the separate-sale model might actually tell you less about overall buying habits than you want to know. If you build the metamodel on the traditional sale definition, where SALE represents all items that one customer purchases at the same place, time, and location, you might gain a more useful perspective of customer behavior. According to the traditional sale definition, your shopping trip—the occasion when you bought a case of printer paper, a roll of transparent tape, and a box of pens—constitutes one SALE.
To accommodate the traditional SALE definition, you have to modify the simple retail-sale metamodel. As Figure 4 shows, I simply adjusted the SALE:PRODUCT relationship to accommodate the expanded definition of a sale. Now, many sales can involve each PRODUCT, and each SALE can involve one or many products.
Finally, you need to decompose the last remaining M:N relationship into its two-component 1:M relationships. To clarify the entities' meaning and purpose, you might also want to add a few attributes for each entity. Figure 5 illustrates what the final retail-sale metamodel should look like. Typically, you want to capture the name and address of every retail location. For payroll reasons, you need to capture each salesperson's name and Social Security number. And if you want to launch a targeted marketing campaign or start a buyers club, you need customer name and address information as well. For each product, you need a description, the quantity on hand, and the quantity-on-hand value that triggers refill orders with the product vendor. The wholesale and retail price per product unit are also handy items of information.
For each sale, you want to capture the date and time of the sale, applicable taxes, applicable discounts, and the total sale amount. But can't you just dynamically calculate totals in a transactional database? Theoretically, you can. But realistically, when the SALE and SALE_ITEM tables hold millions of records, recalculating the values every time you want to know a total sale amount is impractical. Plus, because wholesale and retail unit prices change, sometimes daily, and tax percentages vary geographically, you might not be able to reconstruct the original total amounts. To show that the total attributes are calculated values, I italicize them in Figure 5. Remember, in an ER diagram, you don't show foreign keys—the relationships imply them. Therefore, you can assume that SALE has three foreign key attributes that Figure 5 doesn't show—the location identifier, the salesperson identifier, and, optionally, the customer identifier.
One or many items, which are PRODUCT instances, comprise each sale. For each sale item, you want to capture the quantity sold (for example, two boxes of pens) and the price they were sold at. (The sale price doesn't have to be either the wholesale or the retail price listed in PRODUCT.) The PRODUCT foreign key attributes, which Figure 5 doesn't show, are the sale identifier, which links each item sold to its parent sale, and a product identifier, which labels each item.
A Flexible Design Foundation
A metamodel is a template that you can use as a beginning point for your own production modeling projects. You can create your own set of metamodels by following these simple steps: Identify the basic set of entities that are common to the scenario you're creating the metamodel for, define the relationships between the entities, and add simple and common attributes that describe each entity. Then, when you apply this metamodel to a real design project, you need to incorporate many more attributes and, undoubtedly, more entities. But the beauty of this retail-sale metamodel is that you can use it to jump-start your design projects, and it's totally customizable. Happy modeling!
End of Article
Prev. page
1
[2]
next page -->
