Jump-start your design projects
Entity modeling is a way of graphically representing a database's design, which organizes the tables that hold user data. Making entity relationship (ER) models (also called ER diagrams) isn't difficult; you just have to practice. You can group ER diagrams into categories that represent sales, scheduling, reservations, asset management, inventory control, and so on. And you can represent each category with a template ER diagram, or metamodel. Just as we define metadata as "data about data," in this article we can define "metamodel" as a "model of models."
To start data modeling, you can work on one metamodel. Then, when you're ready to proceed, you can combine multiple metamodels to form the basis of a more complex design project. When you started writing program code, you probably found one or more code templates and modified them to help you create your programs. Entity modeling is similar; you can start with one or more metamodel templates and modify them to help you create your database design.
The Sales Scenario
One of the most common business scenarios is retail sales, so let's develop a metamodel specifically for that scenario. Before you begin to draw this model, you need to decide on the four or five entities that are part of any retail sale. The CUSTOMER entity purchases, leases, or otherwise acquires products. The PRODUCT entity is an inventory listing of objects that are available for sale, lease, or acquisition, such as red sweaters, rototillers, or purple widgets. The SALESPERSON entity sells or leases one or more products to customers or assists customers during a sale event. SALE is the event—a transaction (for example, a sale, a lease, or an acquisition) between customer, salesperson, and product. LOCATION is the place where the sale event occurs—a physical location such as a brick-and-mortar store or a virtual location such as an Internet shopping site.
Defining the Relationships
First, you need to determine the relationships among these retail-sale entities. Figure 1 shows some of the most important relationships. CUSTOMER to PRODUCT is a many-to-many (M:N) relationship because a customer can buy one or many products and one or many customers can buy a product. CUSTOMER to SALESPERSON is also M:N because over a period of time, a customer can buy from one or many salespeople and a salesperson can sell to one or many customers. CUSTOMER to LOCATION is also M:N; a customer can shop at one or more locations, and each LOCATION services many customers.
You've covered customers, products, locations, and salespeople. But where does SALE fit into this picture? If you decompose each of the three M:N relationships into two-component one-to-many (1:M) relationships, you find SALE. As Figure 2 shows, the interaction of a CUSTOMER and a PRODUCT is a SALE. Likewise, a CUSTOMER and a SALESPERSON interaction leads to a SALE. Finally, a CUSTOMER's shopping experience at a LOCATION results in a SALE. SALE is the common activity of these three M:N relationships.
Figure 3 shows the three 1:M relationships from Figure 2 combined into one entity model. At the center of the model is the action entity, SALE. Each sale involves a CUSTOMER, a LOCATION, a SALESPERSON, and a PRODUCT. The metamodel that Figure 3 shows represents the simple retail-sale definition: A customer buys a product at a location from a salesperson. The database records the sale action in separate transactions. For example, you go into an office-supply store and purchase a case of printer paper, a roll of transparent tape, and a box of pens. According to this model, the database records your buying activity as three separate sales (transactions), one for each product you purchased, in the SALE table.
Prev. page
[1]
2
next page 
