The previous chapters focused on information about the parties that conduct business and what products they need. This chapter focuses on how parties obtain these products—in other words, the ordering of products.
Businesses need information to answer many questions about orders; they need to know the terms associated with each order. For instance:
- When is the expected delivery time, and are there consequences for late delivery?
- Who is responsible for paying for the order?
- What is the negotiated price for each product that is ordered?
- What people and organizations are involved in the order?
- Who placed the order? To whom is the order being shipped?
- Was there an approved requirement for the order?
- Were there previous quotes for the order?
- Were there requests to many vendors to bid for the order?
- Are there general agreements in place that govern the terms of the order such as special pricing arrangements?
This chapter discusses the following models:
- Standard order model
- Order and order items
- Order parties and contact mechanisms
- Order adjustments
- Order status and terms
- Order Item Associations
- Requirements
- Requests
- Quotes
- Agreements
- Agreement items
- Agreement terms
- Agreement pricing
- Agreement to orders
Most organizations model orders using the standard data model that is shown throughout many textbooks on data modeling. Figure 4.1 illustrates this standard data model. A SUPPLIER is related to one or more PURCHASE ORDERs, which have PURCHASE ORDER LINE ITEMs that relate to PRODUCT. This model is similar for sales. A CUSTOMER can be related to one or more SALES ORDERs, which have SALES ORDER LINE ITEMs that relate to specific PRODUCTs. Table 4.1 shows the example of a typical sales order. A customer, ACME Company, placed a sales order on June 8, 2001, for several items (or items), each of which is defined in the PRODUCT entity. The sales order stores information such as the date the order was given, the customer placing the order, shipping instructions, and payment terms.
Figure 4.1 Standard order model.
Table 4.1 Standard Order Model
Order line items show the individual products or services that were purchased. Table 4.2 shows examples of ORDER LINE ITEMs. In this example, three items are being ordered: 10 reams of paper, 4 pens, and 6 boxes of diskettes.
Table 4.2 Order Item
When a purchase order is given to a supplier, the same types of relationships exist. A supplier is given many purchase orders, each of which has line items that correspond to a specific product type.
The attributes of sales orders and purchase orders are very similar, if not the same. An order is an order; the only real difference between a sales order and a purchase order is a matter of perspective. In other words, the information on the seller's sales order should, for the most part, correspond to the information on the purchaser's purchase order. The only reason that the attributes may be different is that the two parties may require different information depending on whether one is the seller or the buyer. For example, the enterprise taking the sales order may need to know the commissions for the salespeople involved in the order, whereas when a purchase order is given to a vendor, the purchaser is usually not privileged enough to know what the commission is for the salespeople.
As Figure 4.1 shows, the relationships and data structures between sales and purchase orders are very similar. By having two separate models for both of these types of orders, the model does not recognize the similar properties of sales and purchase orders and developers cannot take advantage of common applications to handle both sales and purchases. For instance, if the structures are the same, developers can create common routines against the same data structures. There could be common routines to look up the terms of an order, to monitor the status of the order against the expected delivery date, or to calculate line-item price extensions.
There is another embedded assumption in this standard order data model: Only one organization is involved in the sales order model, namely the customer, and only one organization is involved in the order in the purchase order model, namely the supplier. Most systems model information from an “I” perspective. This perspective states that the model doesn't have to indicate the party receiving sales orders or placing purchase orders; it's obviously us (meaning the enterprise building the system)! The problem with this perspective is that there may be several internal organizations within the enterprise that may receive a sales order or place a purchase order and this information needs to be recorded for each order. Even if an organization is a small business, there may be a broker who takes a sales order or the organization may grow later on to include subsidiaries, divisions, or other related internal organizations.
There are actually many parties involved in the order who could be involved in many roles. There could be a placing customer, delivery customer, bill-to customer, installation customer, person taking the order, organization that books the order, and similar roles for purchase orders.
Additionally, there are many contact mechanisms involved in sales and purchase orders. Where should the order be billed? Where should the order be shipped? From what contact mechanism did the order originate; for example, did it come from a specific e-mail or phone number? What was the location or contact mechanism that received the order? A model that maintains these various contact mechanisms for the order is needed.
The following order model addresses these previous issues and provides a more flexible structure to maintain purchase and sales order information.
The data model in Figure 4.2 is more flexible, modular, and maintainable than the data model in the previous section and is the one that will be built on throughout the remainder of this book. This model portrays a broader perspective than the “I” model of the previous section and has a more stable, object-oriented approach where orders are related to parties that may play several roles, depending on the circumstances. The following models also provide flexible order data structures for maintaining contact mechanism information, adjustments, statuses, terms and order item associations.
Figure 4.2 Orders and order items.
Figure 4.2 shows that the ORDER entity is subtyped into SALES ORDER and PURCHASE ORDER to cover both sales and purchase orders. Orders are composed of ORDER ITEMs that specify the product(s) that are to be ordered, hence the relationship to PRODUCT. The ORDER ITEM is also subtyped into PURCHASE ORDER ITEM and SALES ORDER ITEM to accommodate information about the ORDER ITEM that is just for a purchase order item or for a sales order item. Each ORDER ITEM may be for one and only one PRODUCT or may be for one and only one PRODUCT FEATURE. PRODUCT FEATUREs may be ordered via an ORDER ITEM such as a certain COLOR, SIZE, SOFTWARE FEATURE, or other product variation that may be included in the order. These features can be specified via the recursive relationship ordered with from an ORDER ITEM that is for a product feature that is ordered for an ORDER ITEM that is for a product feature.
The attribute on ORDER named order date specifies the date on which the enterprise received or gave the order. The attribute entry date is the date on which the order was entered into the enterprise's system. The ORDER entity is subtyped into a SALES ORDER and a PURCHASE ORDER entity in order to accommodate specific attributes or relationships related to either a sales order or a purchase order.
ORDER ITEMs represent the ordering of specific goods or services. Therefore, each ORDER ITEM is defined using one and only one PRODUCT. ORDER ITEMs have a quantity attribute. For goods, this represents the number of goods that are ordered. For services, this represents the amount of hours, days, or other measurement being billed. The unit of measure for this quantity is determined by the unit of measure that is associated with the product (see Chapter 3, Figure 3.4). The unit price attribute stores the charge for an item or the rate for a service. The estimated delivery date is the date that the good is expected to be shipped to the customer or the expected date of service fulfillment to the customer. The shipping instructions attribute stores directions for transporting products to their destination, for example, “do not leave outside,” “fragile-handle with care,” or “requires signature by customer when delivering.” The comments attribute allows additional explanation of the order item. The item description attribute provides a mechanism for storing descriptions for items that are non-standard and would not be maintained within the PRODUCT or PRODUCT FEATURE entities.
This item description attribute could also account for orders that are not product specific such as order items to perform work efforts or to order time for professional services. It will be shown in Chapter 6, Figure 6.3, that order items may also be related to one or more WORK EFFORTs—for example, when a customer places an order for a job to be done.
In Chapter 3, the data model shows that the price for each product can be stored as PRICE COMPONENTs and based on many variables such as geographic location, quantity breaks, the type of party, and outstanding promotions on certain types of products. So why is the unit price not a derived field? The unit price is important as this attribute allows the user to override the calculated price with the negotiated price for this order. Base, discount, and surcharge product price components can all appear as order items associated with a particular product.
Many models will portray orders having “order line items.” The reason that this model does not call them “order line items” is that this term connotes physical lines on an order form. The physical lines on an order form often encompass many other things aside from the items that have been ordered. For instance, there may be notes to records adjustments, taxes, estimated freight costs, explanations, and other descriptive detail for the order. Rather than portray the entity as capturing each of the documented lines on an order form, the ORDER ITEM entity represents a major information need for the order, namely the items or products that have been ordered.
Table 4.3 illustrates the ORDER ITEM entity. The major difference in this entity from the standard order line item entity in Table 4.2 is that both sales and purchase order items are included in this entity. Notice that the purchase order has two items: one for a service (“hourly office cleaning service”) and one for an item (“basic cleaning supplies kit”). This is one reason that orders are not subtyped as service and good orders because one order can include both services and goods.
Table 4.3 Sales and Purchase Orders
ORDER ITEMs may be selected with certain product features that allow customization of the order. This is becoming more and more common in business as the need for customized products rises. Table 4.4 shows examples of product features that may be selected with sales order 12560 or with purchase order A2395. Only features that are optional or selectable are shown as standard, and required features can be derived through the product model. The feature of choosing the color “gray” is a selectable feature for that product, and “extra glossy paper” is a product quality feature that is optional and has a charge. Notice that more than one feature may be recorded for an order item, as has been done for the “Johnson fine grade 8½ by 11 bond paper” item. Each of these features is related to the first item in the order, namely the Johnson paper. Purchase orders may also record features that have been selected such as a billing feature of automatically charging the amounts to a credit card.
Table 4.4 Sales and Purchase Orders
There are two ways that features for ordered products could conceivably be modeled. Ordered features could be modeled as a recursive relationship from a feature-related ORDER ITEM to a product-related ORDER ITEM. Alternatively, ordered features could be handled by maintaining the relationships to both the ordered feature with its related product recorded in the same order item. One issue with the latter approach is that the same product may be ordered on two different order items, and each product may have a different set of features associated with it. For this reason, a recursive relationship is used, relating an order item specifying a feature to another order item for the ordered product.
Order Parties and Contact Mechanisms
As said previously, many parties are involved in an order. The standard order model at the beginning of this chapter shows a simplistic model where there is a CUSTOMER for sales orders and a SUPPLIER for purchase orders. Who is the customer? Is it the party placing the order? The party receiving delivery? The party paying for the order? The party that is located at the point of installation (if there is an installation involved)? The party that uses the product? The same questions need to be addressed with regard to purchase orders, although the roles for the order may be named differently.
Each of these facts may be important. It is therefore essential to record each of these roles in association with the order. The key parties involved in an order are the following:
- The party placing the order
- The party taking the order
- The parties that will receive the delivery of the order
- The party that will pay for the order
- The party for whom the product is installed and/or set up
- The people who were involved in various roles regarding the order such as the person entering the order, quality assuring the order, managing the order, and so on
Figure 4.3 illustrates a data model for these order roles and associated contact mechanisms for sales orders. The next section will address a similar data model for purchase orders (Figure 4.4), and then a model will be presented that can handle either sales or purchase orders (Figure 4.5).
Figure 4.3 Sales order parties and contact mechanisms.
Figure 4.4 Purchase order parties and contact mechanisms.
Figure 4.5 Generic order roles and contact mechanisms.
Sales Order Parties and Contact Mechanisms
Figure 4.3 provides a specific data model showing the roles and the contact mechanisms involved in a sales order. Each SALES ORDER and SALES ORDER ITEM have many PARTY ROLE subtypes as well as many CONTACT MECHANISMs associated with them. A SALES ORDER may be placed by a PLACING CUSTOMER, taken by an INTERNAL ORGANIZATION CONTACT, and with a requested bill to a BILL TO CUSTOMER. This clearly represents the business rules of most standard sales orders.
The SALES ORDER may be placed using a CONTACT MECHANISM, which may be an address or phone number, and this number may also be used for confirmation of the order (or alternatively another confirmation number relationship could be added). The ORDER may be designated to be billed to a certain CONTACT MECHANISM, which may be a specific postal address or e-mail address. The order is also taken via a CONTACT MECHANISM that helps establish the phone number or email server that handled the order.
The ORDER ITEM may be designated to be shipped to a SHIP TO CUSTOMER and designated to be shipped to a specific CONTACT MECHANISM, which could be a postal address or e-mail address. The SHIP TO CUSTOMER is not related to the SALES ORDER entity but to the SALES ORDER ITEM because different SALES ORDER ITEMs may be designated to go to different parties. The designated to be shipped to CONTACT MECHANISM may also vary by ORDER ITEM within the same ORDER. For example, the customer may order one item to be delivered to one address and a different item delivered to another address.
If installation is needed, the enterprise may want to consider adding another relationship to record the installation address, which may be different from the shipping address. If this is needed, then the modeler can add the relationship: the SALES ORDER ITEM may be designated to be installed at an INSTALLATION CUSTOMER and be designated to be installed at a CONTACT MECHANISM, which would probably be a postal address.
Are the relationships from ORDER to PARTY and CONTACT MECHANISM independent relationships, or should an ORDER be related to a PARTY CONTACT MECHANISM that provides for both of these together? For example, instead of having two separate relationships to show the placed by PARTY ROLE and the placed using CONTACT MECHANISM, should the sales order be tied to a PARTY CONTACT MECHANISM that stores the party placing the order at their associated contact mechanism? The issue with doing the latter is that one may have one fact without the other. The enterprise may know who the placing party is and may not know its contact mechanism. In some cases, such as Internet-based orders, the enterprise may know the contact mechanism and not know who the party is! Therefore, these are modeled as independent facts of the order.
Note also that the PARTY CONTACT MECHANISM represents the addresses, phone numbers, fax numbers, and other contact mechanisms associated with the party for ongoing contact purposes. The relationship to CONTACT MECHANISM in this model represents the specific contact information to be used for the order and may or may not also belong in the master contact information that is in PARTY CONTACT MECHANISM. For example, the customer may want to specify a contact mechanism that is solely for use for that order.
Party Placing Order and Related Contact Mechanism
Orders may be placed by an individual or an organization depending on the business requirements of the organization for whom the system is built. In mailorder catalog businesses, the order is usually placed by a person. In manufacturing firms, the order is usually placed by an organization. The order may also be placed by an agent or a broker of a party. The PARTY RELATIONSHIP entity can identify if certain types of parties are allowed to place an order for the enterprise, such as checking to see if a valid broker relationship exists.
The SALES ORDER is related to the CONTACT MECHANISM to maintain order confirmation or follow-up information. This information may or may not be different from the party's contact information that is stored in the PARTY CONTACT MECHANISM, which stores all the default contact information for a party. The PARTY CONTACT MECHANISM information could serve to warn if a new contact mechanism is used for an order. A party could elect to designate the contact mechanism that placed the order for order confirmation purposes that they do not want in their contact information. An example of this could be a temporary location that is only for the purpose of the order and not for ongoing contact information purposes.
Party Taking Order and Related Contact Mechanism
The order is always taken by a party whether it is taken at the enterprise for a sales order or taken by the supplier for purchase orders. Figure 4.3 shows that an ORDER must be taken by a PARTY ROLE or INTERNAL ORGANIZATION and may be taken via a particular CONTACT MECHANISM. The order may be taken by a particular subsidiary, division, or department. A particular store may take an order, or the corporate headquarters may take the order. The order may also be taken through the World Wide Web; therefore, the order would be associated with a contact mechanism of the URL and the internal organization that received the order would also be recorded.
Ship-to Party and Contact Mechanism
In certain circumstances, orders are placed by one party and delivered to other parties. It is possible for someone to order a gift that is to be shipped to another party. It is also possible for an authorized agent of an organization to place an order that may be delivered to its client's address or addresses.
Figure 4.3 shows that each ORDER ITEM may be with a requested bill to a BILL TO CUSTOMER and designated to be shipped to one and only one CONTACT MECHANISM. Physical items will always require a destination where the items need to be shipped. Orders for service are also generally delivered to a specific location. The designated to be shipped to relationship is at the item level because one order could have many destinations. For example, a large department store chain may order 5,000 cases of cola with instructions to deliver 1,000 cases to each of its five regional stores. With this model it is possible to then record five items of 1,000 cases each, then specify a different delivery address for each item. If this relationship was to the ORDER entity, it would be necessary to record five different orders, which would result in storing redundant information and increase the margin for error during data entry.
It may seem as though the shipping information should be specified as part of the SHIPMENT model, for instance as a scheduled shipment, but there is a reason why it is not. As will be seen in the next chapter, a SHIPMENT can be made up of order items from many orders. That is, if several order items from different orders are to be sent to the same location, these can be consolidated into one delivery. Therefore, it is important to record the requested ship-to information on the order because it may affect how shipments are delivered. In other words, the information on designated to be shipped to parties cannot be specified by a SHIPMENT because it has not yet been determined which orders will be combined together in which shipments.
Note that this relationship is optional because there are cases where the location of the delivery is immaterial. For example, an order to provide cleaning services for a corporation may be provided across the entire enterprise, so there may not be a need for a designated to be shipped to relationship because there is not a single delivery address for the work.
The shipped from relationship is not specified in this chapter because this will most likely be determined after the order is created, when the SHIPMENT is determined. This relationship is included in the data model in Chapter 5, which covers shipments.
Bill-to Party and Contact Mechanism
The order needs to specify the party responsible for paying for the order as well as the contact mechanism that serves as the billing location for the order (generally where the invoices are sent). The relationship from SALES ORDER with a requested bill to a BILL TO CUSTOMER identifies the party responsible for paying for the invoice. The relationship from SALES ORDER designated to be billed to a CONTACT MECHANISM identifies where to send the bill and where to follow up for payment.
Table 4.5 gives examples of orders and the associated party addresses or contact mechanisms. Note that the order item seq id 3 order 12560 has a different ship-to than the other order items and is allowable because the ship-to is determined at the order item level. Order 23000 has a different ship-to party and contact mechanism than bill-to. Furthermore, this was an Internet-based order, taken via a contact mechanism that is a web URL. Order 24830 was placed by an e-mail address as the contact mechanism that placed the order.
Table 4.5 Order Roles
In addition to the key order relationships named previously, many other parties could be involved in the order process. Examples include the person giving the order, the person processing the order, the person approving the order, the parties that are scheduled to coordinate installation, and the parties responsible for fulfilling the order. Therefore, other parties may play various roles in an order. Figure 4.3 includes a data model structure that maintains the other roles parties play in orders.
Each ORDER may be involving one or more ORDER ROLEs, which are described by an ORDER ROLE TYPE. Each ORDER ROLE must be involving one and only one PARTY. While many times these roles will involve people, organizations may also play some of these roles, such as a service team that is responsible for ensuring fulfillment of an order.
Table 4.6 gives examples of the various roles that parties could play in an order. In order 23000, John Jones and Nancy Barker were the salespeople involved in closing the sale. Because they are both equally involved, the percent contribution attribute stores the fact that they were both given 50 percent of the credit for the sale. This information could be used in a commissions system. Frank Parks is responsible for processing the order or, in other words, entering the data into the system. Joe Williams is the party responsible for reviewing the information in the order. The order needs to be authorized or approved as a valid commitment by John Smith. Notice that the roles dictate the party responsible for accomplishing these functions so even if the function has not been performed yet, a party can still be slotted to play a role.
Table 4.6 Order Roles
The roles described are just a sample of possible roles, and parties could play many other roles in the order. For instance, in some cases, the product needs to be set up or installed, and therefore installation parties are needed. These relationships identify the party that is responsible from the customer for coordinating the setup and repair of the order as well as the place. For instance, if a customer orders a personal computer, either the end user or the customer PC support person may be the installation contact, and the address or phone number of that person could be given. The model could be expanded to allow for a many-to-many relationship of PARTYs as well as CONTACT MECHANISMs to store numerous parties and contact information if necessary.
Purchase Order Parties and Contact Mechanisms
As discussed earlier, the data contained in purchase orders is very similar to the data in sales orders because they both represent the same type of transaction, only from different points of view.
Figure 4.4 provides a data model with purchase order roles and contact mechanisms. Not surprisingly, the data structures are very similar to those of the previous model. Because either a sales order or a purchase order represents a commitment between parties, regardless of whether one is on the buying or selling side, each party has a need to track the details of the order. The only difference is the name of the roles involved. For instance, instead of the INTERNAL ORGANIZATION taking the sales order, the SUPPLIER is that party that takes the purchase order. Instead of the PLACING CUSTOMER, the PLACING PARTY is shown to account for the party that gives the order, which may be an internal organization or an agent of the enterprise. Instead of a BILL TO CUSTOMER, the BILL TO PURCHASER role is used, and instead of a SHIP TO CUSTOMER, the SHIP TO BUYER maintains the party to whom the order is shipped.
Table 4.7 provides an example of the roles involved in purchase order A2395. In addition to these roles there would be additional ORDER ROLEs for the people involved in the purchase order, similar to the sales order model.
Table 4.7 Purchase Order Roles
Generic Order Roles and Contact Mechanisms
Should the sales order and purchase order models be combined to take advantage of similar data constructs that could simplify the systems design? Should the data model show relationships from the order to specific party role entities (SHIP TO CUSTOMER, BILL TO CUSTOMER, and so on), or leave the model flexible and relate the order to many ORDER ROLEs of ORDER ROLE TYPEs, thus facilitating easier database changes over time? Should the model show specific relationships to order role entities as well as flexible role types that could be changed easily?
This is really not just an issue limited to orders. Any transaction type entity will have many parties associated with it. This may be modeled more generically by showing a relationship to a transaction role (ORDER ROLE, SHIPMENT ROLE, INVOICE ROLE) of a ROLE TYPE and then associated with a PARTY. Alternatively, specific PARTY ROLE subtypes could be related to the transaction. For example, an ORDER may be related to the party role, BILL TO CUSTOMER, SHIP TO CUSTOMER, and so on.
The advantage of showing specific relationships to party roles such as BILL TO CUSTOMER and SHIP TO CUSTOMER is that the model can show and enforce business rules. For instance, perhaps there is a business rule that says there is one and only one BILL TO CUSTOMER associated with an order. Or perhaps the enterprise allows more than one BILL TO CUSTOMER, and this can be shown as well. The point is that the business rule of the enterprise is explicitly stated in the data model.
The other advantage of showing specific relationships is that the model is less abstract and easier to read. It is easier to comprehend that an order is associated with a BILL TO CUSTOMER instead of that orders are associated with many ORDER ROLEs of ROLE TYPEs and one of the instances of ROLE TYPE is “bill-to customer.”
The advantage of developing the data model with the transaction (i.e., ORDER, SHIPMENT, INVOICE) associated with more than one ROLE (ORDER ROLE, SHIPMENT ROLE, INVOICE ROLE) is that the data model, and the ensuing database design, is then much more flexible. The business rule could change from one bill-to customer to the allowance of many bill-to customers over time. If this flexible model is used, the underlying data structure does not have to be changed (although there may be a change needed to some code or a stored procedure). This structure would also allow additional roles to be added without changing the underlying data model or data structure.
Therefore, if the business rule is stable and not expected to change over time, then using the more specific data modeling method is preferable. Be careful, as it is common for one to think a data structure will not need to be changed and then invariably things change! If it is anticipated that the data model structure may need to change over time, then the more flexible way of modeling roles should be used.
Figure 4.3 and 4.4 showed specific data models for modeling the sales and purchase order roles; Figure 4.5 shows an alternate, more flexible data model for modeling these roles and contact mechanisms.
Figures 4.5 shows that each ORDER may involve one or more ORDER ROLEs, each of which is assigned to a PARTY and is described by an ORDER ROLE TYPE. This structure accommodates handling any number of roles for either sales orders or purchase orders by storing the possible roles in ORDER ROLE TYPE and then relating them to the ORDER ROLE. Possible ORDER ROLE TYPES include “placing party,” “bill-to customer,” “internal organization taking order,” “placing customer,” “placing buyer,” “supplier,” “bill to purchaser,” “order entry person,” “order salesperson,” and “order autho-rizer.” Each ORDER ITEM may also have ORDER ITEM ROLEs of ORDER ITEM ROLE TYPEs, which could include “ship-to buyer,” “ship-to customer,” “installation customer contact,” and “installer.”
Each ORDER may have one or more ORDER CONTACT MECHANISMs, and each ORDER ITEM may have one or more ORDER ITEM CONTACT MECHANISMs in order to record the address, phone, fax, e-mail, or other contact mechanism to confirm, ship, bill, take, or install the order. The CONTACT MECHANISM PURPOSE TYPE maintains values such as “ship to,” “bill to,” “confirmation,” “placing,” or “taken via” to describe the role of the contact mechanism. The CONTACT MECHANISM entity stores the actual address, phone number, fax number, e-mail address, or other contact mechanism that was used in conjunction with the order.
As the business rules of the enterprise change, the organization may want to track additional roles or there may be changes to the cardinality rules. This generic model is flexible enough to handle new data requirements easily. For example, a new order entry process may dictate that there may be at least two contact mechanisms that can serve to confirm orders. This structure accommodates as many contact mechanisms and roles as are needed in any circumstance. Restrictive business rules such as allowing only one bill-to customer could be accommodated outside the data structures in stored procedures or common routines. This rule, for example, could also change over time to allow more than one party to be billed for an order, and the model would be able to easily handle this requirement.
There are other parts of the order that do not reflect the ordering of specific products or features. Examples include discounts, surcharges, processing fees, and shipping and handling charges. Should these be considered subtypes of ORDER ITEMs or be considered their own entity and related to ORDER ITEMs?
If these adjustments are portrayed as subtypes of ORDER ITEM, then a recursive relationship would need to exist between ORDER ITEMs in order to relate the adjustments to the appropriate other ORDER ITEM. This data model is somewhat complicated because many of these adjustments may affect either or both the ORDER and ORDER ITEM. Is an adjustment really a similar enough thing to the ORDER ITEM, which represents the product being ordered? Another way of looking at this is that the item being ordered is an ORDER ITEM and an ORDER ADJUSTMENT is a separate entity since an adjustment doesn't really represent something that is “ordered.”
For these reasons, the ORDER ADJUSTMENT is shown in Figure 4.6 as a separate entity. The subtypes are DISCOUNT ADJUSTMENT, SURCHARGE ADJUSTMENT, SALES TAX, SHIPPING AND HANDLING CHARGES, FEE, and MISCELLANEOUS CHARGE. The DISCOUNT ADJUSTMENT and SURCHARGE ADJUSTMENT are subtypes that store price adjustments to either the complete ORDER or to each ORDER ITEM. These price adjustments may be either an amount or a percentage.
Figure 4.6 Order adjustments.
The first three items in Table 4.8 are examples of discounts and a surcharge. The first shows that there is a $1.00 discount off each unit of a certain item (the next section will cover how to relate it to the item to which it refers). The second item shows that there is a 10 percent discount off the entire order, and the third item shows that there is a $10.00 surcharge for delivery outside the normal area.
Table 4.8 Order and Order Item Adjustments
The SALES TAX provides information on the sales tax charged either on the whole order or on a particular order item. SHIPPING AND HANDLING CHARGE would add an order adjustment to the ORDER or ORDER ITEM. FEE could store adjustments for charges such an “order processing fee,” “handling fee” for covering the arrangements of the shipment of an order, or a “management fee.”
The MISCELLANEOUS CHARGE subtype provides a mechanism to store information about any other charges that could occur on an order. An example of this could be “adjustment error” to correct a prior order. The ORDER ADJUSTMENT TYPE entity provides the ability to classify the various types of adjustments into detailed categories. The description attribute defines the possible values that may be related to adjustments.
An additional data structure is provided in Figure 4.6, which provides information on how much sales tax to charge. The SALES TAX LOOKUP entity stores a sales tax percentage that could vary by GEOGRAPHIC BOUNDARY such as COUNTY, CITY, or STATE and also may vary by PRODUCT CATEGORY. For instance, food products may have different tax implications than non-perishable products. Some types of products may be tax-exempt and could be classified this way by relating them to a “tax exempt” PRODUCT CATEGORY.
Table 4.8 provides examples of adjustments that could be stored in the model. The first row shows that the third order item, “Jerry's box of 31?2-inch diskettes,” has an order adjustment of a discount of $1.00 applied to it. The next three rows are adjustments that were applied to the whole order because no order item is signified. There was a 10 percent discount off the entire order, a surcharge of $10.00 applied because the delivery location was outside the acceptable geographic area, and a fee associated with it of $1.50 for order processing charges.
Figure 4.7 builds on the order model by illustrating information needs for the status and terms of the ORDER and ORDER ITEMs. This figure includes an ORDER STATUS entity to track the progress as well as to know the current state of an ORDER or ORDER ITEM. The ORDER STATUS TYPE maintains the status of possible order. The ORDER TERM entity tracks the conditions of business associated with the ORDER or ORDER ITEM. The TERM TYPE entity maintains the possible terms available to use.
Figure 4.7 Order status and terms.
Because an order may be in many different states at different points in time, the model in Figure 4.7 shows that the order may have more than one status over a period of time. The status datetime attribute of ORDER STATUS provides for the tracking of when each status of the order occurred. For example, this model will allow the tracking of when the order was received, when it was approved, and when and if it was canceled. The description attribute of the ORDER STATUS TYPE entity stores the possible statuses such as “received,” “approved,” and “canceled.” If it is necessary to know only the current status of the order and not a history of when each status occurred, then there could simply be a many-to-one relationship from ORDER to ORDER STATUS TYPE (and ORDER ITEM to ORDER STATUS TYPE).
Why not have “shipped,” “completed,” “backordered,” or “invoiced” as order statuses? As will be discussed in later chapters, because shipments and invoices are tied to orders, “shipped,” “completed,” and “invoiced” can be derived through the relationships to shipment items and invoice items. “Back-ordered” can be derived through the ORDER ITEM ASSOCIATION entity discussed later in this chapter. Although these statuses are derivable values in the logical model, the physical database design may have these statuses available in the ORDER STATUS TYPE table for easy access.
The parties involved may agree on many arrangements or terms. Delivery terms, exchange or refund policies, and penalties for non-performance are some examples. Each ORDER or ORDER ITEM may have one or more ORDER TERMs, each of which is categorized by a TERM TYPE. The term value attribute is applicable only to some of the order terms, and its meaning is dependent on the type of term.
Table 4.9 shows examples of order terms. Order 12560 has three terms defined to it. The first two terms document that if the purchaser cancels the order 10 days after placing it, there is a 25 percent cancellation charge. The third term says there are no refunds or exchanges after this item has been delivered.
Table 4.9 Order Term
While one may think that that terms are associated with only the order header, in some cases the individual order items also have terms associated with them. Table 4.9 shows several examples of terms that are associated with the order item. The “Goldstein Elite pen” order item has a term that it cannot be exchanged or refunded once delivered.
On the purchase order A2395, for the order item “hourly office cleaning service” there is a 5 percent non-performance penalty if the order is delivered more than 30 days past the estimated delivery date.
Sometimes there is a relationship from one order item to another order item. The ORDER ITEM ASSOCATION entity in Figure 4.8 relates sales order items to purchase order items.
Figure 4.8 Order item association.
An example of this association occurs when a sales order item is dependent on a purchase order item. For example, a distributor may receive a sales order but may not have enough inventory in stock to cover one of the items on it. In turn, the distributor may place a purchase order to one of its suppliers (or many purchase orders to many suppliers) to fulfill the item that was short. In other words, the sales order item was “backordered” and covered by a purchase order item.
A single item on a purchase order may be used to fulfill several items on sales orders. Alternatively, a sales order item may be fulfilled by many purchase orders items because the additional inventory may be ordered from many different suppliers. The ORDER ITEM ASSOCIATION handles this many-to-many relationship.
The example shown in Table 4.10 illustrates the data that would occur from a purchase order item tied to a sales order item. Order 23490 is a purchase order that has an item that will provide the items needed for sales order 13480, item 1.
Table 4.10 Order Item Association
Another situation where sales and purchase orders are linked is when a sales order requires the corresponding purchase order number from the buyer. Because the seller may want to track the buyer's corresponding purchase order ID, the attribute corresponding PO ID is on the SALES ORDER ITEM entity. This attribute is defined at the order item level and not the order header because each order item of the sales order may be related to a different purchase order. The ORDER ITEM ASSOCIATION entity is not used to relate the purchase order to a sales order in this case because the seller is generally not interested in recording the full details behind the purchase order-the seller usually needs only the purchase order number.
In the latter case, one sales order item is generally not related to multiple purchase orders. If an item on a sales order is placed due to two or more purchase order items, the sales order item may be split into separate sales order items to be able to trace the exact amount of the item that corresponded to each purchase order.
The data models and information about orders covered thus far in this chapter are very common to most enterprises. The remainder of this chapter will illustrate data models that may or may not be applicable to an enterprise, depending on the information requirements of each specific enterprise. The data models already presented may represent a complete ORDER data model for some enterprises.
The next sections illustrate additional aspects around the order subject data area. These include: REQUIREMENTs to identify either customer or internal needs, which could lead to either sales or purchase orders, REQUESTs, which represent the solicitation of suppliers or solicitation to the enterprise to bid on orders, QUOTEs, which are the responses to requests to bid, and AGREEMENTs, which define terms and conditions that may govern orders. Each enterprise needs to evaluate which of these data models are applicable to its business and should be incorporated into its design.
An order occurs because a party has a need for something. Some enterprises will track these needs, and some may not. The enterprise may track its customer needs as well as its own needs. An example of a customer need is capturing a requirement of a customer to help build a system for him or her. An example of an internal need is a requirement to purchase certain office supplies.
The organization may be interested in tracking either customer or internal needs, or both, depending on the type of enterprise. In some enterprises, such as professional service organizations, it is very important to capture the needs of customers. In other organizations, such as mail-order catalog enterprises, the enterprise may not track its customer needs and track only needs for their internal requirements.
Figure 4.9 provides a very flexible data model that handles the capturing of both customer and internal requirements.
Figure 4.9 Requirements.
A REQUIREMENT is an organization's need for anything. The REQUIREMENT entity represents a need that may be either a CUSTOMER REQUIREMENT or an INTERNAL REQUIREMENT. Similarly, each REQUIREMENT could be either a PRODUCT REQUIREMENT or a WORK REQUIREMENT. A PRODUCT REQUIREMENT may be related to a specified PRODUCT and/or several DESIRED FEATUREs because the need may be expressed as either a need for a product or a need for something with certain types of characteristics.
REQUIREMENTs may be made up of other REQUIREMENTs and hence the recursive relationship around REQUIREMENT. For instance, there may be a WORK REQUIREMENT to buy office supplies. This requirement may be further broken down into more specific requirements to buy paper, pencils, pens, and CD-ROMs. Similarly, a WORK REQUIREMENT to “develop a new sales analysis system” may be further broken down into more specific requirements to “conduct needs analysis,” “select application packages,” “select hardware and software,” and “implement system.” With this recursive structure, the enterprise may define multiple levels of requirements.
Many people may be associated with the REQUIREMENT in various REQUIREMENT ROLEs of REQUIREMENT ROLE TYPEs such as “owner,” “originator,” “manager,” “authorizer,” or “implementer.” The REQUIREMENT may have several REQUIREMENT STATUSes over time such as “active,” “on hold,” or “inactive.” Each REQUIREMENT may be needed at a certain FACILITY such as a particular plant, warehouse, office, or room of a building.
The REQUIREMENT may be related to one or more ORDER ITEMs via the associative entity ORDER REQUIREMENT COMMITMENT. This entity would maintain which SALES ORDER ITEMs fulfilled which REQUIREMENT from CUSTOMER REQUIREMENTs. It would also maintain which PURCHASE ORDER ITEMs are fulfilled by the REQUIREMENT from INTERNAL REQUIREMENTs.
REQUIREMENTs have a description attribute that defines the need of the requirement. The requirement creation date specifies when the requirement was first created, and the required by date within the REQUIREMENT entity specifies the date by which the requirement item is needed. The estimated budget attribute identifies how much money is allocated for fulfilling this requirement. The description allows a full explanation and comments for the requirement. The attribute quantity determines the number of items needed in the requirement and allows the requirement to specify that several products or things are needed. For instance, there may be a requirement to hire three programmers. The reason attribute explains why there is a need for the requirement.
Similar to orders, requirements may have many people involved in them. For instance, one person may be responsible for creating the requirement, and another person may be responsible for approving the requirement. The REQUIREMENT ROLE entity maintains which people play which roles in the requirement. Therefore, the relationship to PARTY identifies the person or organization that is responsible for playing the role. The from date and thru date attributes on REQUIREMENT ROLE define the time period for which the party is responsible for playing the role. The REQUIREMENT ROLE TYPE entity maintains the possible values for these role types.
Some example roles that parties could play in a requirement are “owner,” “originator,” “manager,” “authorizer,” or “implementor.” The owner is the party for whom the requirement is created. The originator is the person or organization that identified the need or requirement. The manager is the person responsible for monitoring the requirement and seeing that it is fulfilled. The authorizer is the person who approves or denies the requirement. The implementor is the party who makes sure that once authorized, the requirement is fulfilled.
Requirements also have a status such as “active,” “on-hold,” or “inactive.” The REQUIREMENT STATUS entity stores a history of the various statuses of the requirement and the date each status occurred. The REQUIREMENT STATUS TYPE entity maintains the types of statuses that could occur. If the history of the requirement statuses is unimportant and the enterprise is interested only in the current status, then an alternate data model would reflect a many-to-one relationship from REQUIREMENT to REQUIREMENT STATUS TYPE.
Notice that the relationship to PRODUCT is optional and only applies to PRODUCT REQUIREMENTs. Chapter 6 more fully describes the work requirement and work effort data models.
PRODUCT REQUIREMENTs may specify the need for certain features as illustrated in the relationship to DESIRED FEATURE. The optional ind (indicator) attribute in the DESIRED FEATURE indicates that the feature is desired and not required if the value is “yes.” For example, the “Goldstein Elite pen” could be specified together with a DESIRED FEATURE of color “blue” with an optional ind of “yes” stating that this is nice to have but not absolutely required.
A requirement or a need to obtain products can naturally result in orders. One REQUIREMENT may lead to many ORDER ITEMs. For example, the need for 1500 #2 pencils may be fulfilled by several purchase order items. Alternatively, there may be several outstanding requirements for #2 pencils that may all be ordered in a single order item. Figure 4.9 therefore shows a many-to-many relationship between REQUIREMENT and ORDER ITEM.
The ORDER REQUIREMENT COMMITMENT determines how many items are allocated from an order item to a requirement. For instance, suppose there are two requirements, each with items for 1500 #2 pencils (3000 total pencils needed), and one order item for 2000 of these items that partially fills the requirements. Then it is necessary to specify how many of the order items were used to fulfill each requirement. The quantity attribute in ORDER REQUIREMENT COMMITMENT serves this purpose.
Table 4.11 shows some examples of requirements. The first two requirements are internal requirements, one for the parent company, ABC Corporation, and one for the subsidiary, ABC Subsidiary. Requirement 24905 is a requirement to have PCs repaired. The requirement specifies a request to fix 14 PCs. This work requirement may be fulfilled by one or more order items.
Table 4.11 Requirements
Requirement 43005 shows a requirement with related sub-requirements. The requirement to have the office cleaned on a regular basis involves the two sub-requirements of hiring a cleaning service and procuring cleaning supplies.
The next requirement, requirement 30003, is a customer requirement from ACME Corporation, which is need of office supplies to run its own office. The overall requirement is linked (through the recursive relationship) to several lower level requirements of 50 reams of bond paper, 40 Goldstein Elite Pens, 20 reams of copier paper, and 200 diskettes.
The 50 reams of bond paper is specified though the free-form description attribute in the REQUIRMENT entity and further specified by relating the DESIRED FEATUREs of “81?2 by 11-inch” dimension and quality of “Fine Grade.” The second lower level requirement shows that the specific product of “Goldstein Elite pen” is desired in the color “blue.” The third and fourth lower level requirements for “reams of copy paper” and “diskettes” are specified with a combination of the REQUIREMENT description and DESIRED FEATUREs. The example illustrates that each REQUIREMENT may be specified by relating it to a specific PRODUCT, describing it with a description, and/or specifying DESIRED FEATUREs.
The last requirement in the table shows a similar requirement to fix PCs that may have been generated from another part of ABC Corporation.
Instead of immediately ordering the products on a requirement, sometimes a process of requesting and receiving quotes is used. A request is a means of asking vendors for bids, quotes, or responses to the requirement. The request could be sent to the enterprise, or it could be sent out from the enterprise to solicit responses from suppliers.
Figure 4.10 shows the key entities associated with requests. The REQUEST may have many REQUEST ROLEs, each of which is of a REQUEST ROLE TYPE and is related to a PARTY. Examples of roles include “originator” (the party that the request is from), “preparer,” “manager,” and “quality assurer.” The RESPONDING PARTY maintains information about the parties that may be responding. The CONTACT MECHANISM records what contact mechanism will be used or was used to send the request to the responding party. Each REQUEST may be composed of one or more REQUEST ITEMs. Each REQUEST ITEM represents one or more REQUIREMENT ITEMs that may be combined in the request. Because REQUIREMENTs may be related to multiple REQUEST ITEMs, the associative entity, REQUIREMENT REQUEST, resolves the many-to-many relationship.
Figure 4.10 Requests.
There are several types of requests. The REQUEST entity has three subtypes that are the most common forms of requests. The RFP subtype stands for “request for proposal” and asks vendors to propose a solution to the needs specified in the details of the request. The RFQ subtype stands for “request for quote” and asks for bids from vendors on specific products. The RFI subtype stands for “request for information” and is generally sent out before an RFP or RFQ in order to determine preliminary information about the qualification of vendors. This is often used as a mechanism to screen the vendors that will be allowed to respond to an RFP or RFQ.
The REQUEST entity maintains the request date, which is when the request was created, a request required date, which is the deadline when vendors need to respond to the request, and a description, which details the nature of the request.
Table 4.12 illustrates the information contained in a REQUEST. The first row is a request for information to determine which vendors are qualified to bid on repairing PCs. The second row is a request for proposal that follows the RFI and asks vendors to bid on solutions to the PC repair maintenance needs. The third row illustrates an RFQ, which is an incoming request that asks the enterprise to quote prices and terms for specific office supply products.
Table 4.12 Requests
Requests are sent by a certain party and to certain parties. The party that it is sent by is stored at the REQUEST ROLE of “originator.” The party(s) that it is sent to is stored in the RESPONDING PARTY entity. A RESPONDING PARTY is one that is asked to respond to a request with information or a bid, quote, or proposal for the request. The RESPONDING PARTY entity is principally used for outgoing requests because incoming requests will usually have only one responding party, namely the part of the enterprise that received the request and will respond to it. The date sent attribute indicates when the enterprise sent the request to the responding party.
Requests have items describing the various things they need in the request. The REQUEST ITEM has an attribute of quantity stating how many of the items are needed. The required by date indicates when the items need to be delivered to the requesting organization. The maximum amount attribute describes an upper limit price for the item, beyond which the enterprise will not even consider.
The request may be for a specific product, as in an RFQ, or it may be a request asking vendors to provide solutions to a specific problem. If the request is for a product, then the relationship to PRODUCT accommodates these types of request items. If the request is for a proposal to specific problems as in an RFP, then the description attribute maintains a description of the problem.
REQUIREMENTs are related to REQUEST ITEMs. One REQUIREMENT may be related to more than one REQUEST ITEM. For example, the first two rows of Table 4.13 show that requirement 24905 had two requests associated with it. A request for information was sent out first, then a request for quote followed.
Table 4.13 Request Items
One REQUEST ITEM may be associated with more than one REQUIREMENT. Table 4.13 shows that the request for information 23498 combined the need for items in requirements 24905 and 34988, resulting in a request to fix 29 PCs, 14 from one requirement and 15 from another. Also note that more than one request was sent out for these same requirements. One request was for an informal RFI (request for information) and one for an RFQ (a more formal request for quotation). This is an example of the need for the intersection entity REQUIREMENT REQUEST, as shown on Figure 4.10.
Request id 38948 provides an example where each REQUEST ITEM within it corresponds directly to an associated REQUIREMENT in a 1:1 fashion. Request item seq ID “1” corresponds to REQUIREMENT “30004,” request item seq ID “2” corresponds to REQUIREMENT “30005” and so on. However, the many-to-many associative entity, REQUIREMENT REQUEST, still accommodates this information.
The data model in Figure 4.10 provides the flexibility to have the same REQUIREMENT requested many times in several REQUEST ITEMs. The latter example showed that, for the same requirement, there may be a request for information and then a request for proposal.
A simpler, alternate model that may meet the needs of many organizations could be to relate REQUESTs directly to REQUIREMENTs. This only makes sense if the enterprise knows that there will not be multiple REQUEST ITEMs for the same REQUIREMENT and also if REQUIREMENTs may not be combined into a single REQUEST ITEM.
A quote is a response to a request; it is synonymous with a bid or proposal. The quote provides the pricing and terms associated with products that fill the need for the request.
Figure 4.11 shows the data model associated with quotes. QUOTEs are issued by a PARTY and given to a PARTY. The enterprise, or a part of the enterprise, may be the receiver or giver of a QUOTE. Other roles are associated with the quote that are stored in the QUOTE ROLE entity, and allowable values for roles are maintained in the QUOTE ROLE TYPE entity. The entity QUOTE stores the basic information related to the quote and is composed of QUOTE ITEMs that describe the specific PRODUCT or WORK EFFORT being quoted. QUOTE ITEMs must be related to a single REQUEST ITEM because quote items are defined as responses to these request items. Each QUOTE ITEM may, in turn, lead to one or more ORDER ITEMs because the quoted item may be ordered more than once. Similar to orders, each QUOTE or QUOTE ITEM may have terms associated with it that create the need for the relationships to QUOTE TERM and to TERM TYPE.
Figure 4.11 Quotes.
Similar to requests, two very significant relationships are explicitly stated, namely, what party issued the quote and the party to whom the quote was given. Just as orders, requirements, and requests have other various roles, quotes also have people playing various roles. Examples of roles for a quote might be “quoted by,” “reviewed by,” and “approved by.”
The QUOTE entity stores header information about the quote. For example, the issue date maintains when the quote was communicated to the intended party. The valid from date and valid thru date maintain when the quote can first be acted on and when the quote expires. The description attribute describes the nature of the quote.
A QUOTE has a subtype of PROPOSAL that is generally more elaborate and includes many sections, such as statements of need, proposal descriptions, benefits, cost justifications, resources required, and so forth. Another subtype is a PRODUCT QUOTE, which is simpler and just tracks the terms and prices behind the products being quoted. There may be other types of quotes, such as bids or offers, depending on the enterprise and its terminology. Table 4.14 gives some examples of quotes.
Table 4.14 Quotes
The first quote is a proposal because within the quote there may be a description of the approach to repairing the PCs. The second quote is a product quote because it is simply quoting prices for specific pens and pencils.
The QUOTE ITEM entity contains information for specific products and is therefore related to PRODUCT. The QUOTE ITEM is also associated with a REQUEST ITEM. One may conclude from looking at this model that the relationship to PRODUCT is unnecessary because it is possible to derive the product that a quote item references. This may be done by traversing the model from QUOTE ITEM to REQUEST ITEM to PRODUCT. The product from the quote, however, may be different from the product requested. For instance, the product requested may be a “#2 pencil.” The product quoted may be more specific, such as a “Johnson Red Striped #2 pencil.”
QUOTE ITEM has a many-to-one relationship with REQUEST ITEM. When a request is sent out, the quote, bid, or proposal should correspond to only one specific request from an organization, whether it is verbal or written. A request, such as an RFP, though, may have many quotes associated with it because many suppliers may respond.
Table 4.15 shows examples of quotes that correspond to the requests of Table 4.12. Each quote item corresponds to a request item. The original request (“38967”) called for a quote to fix 14 PCs. The corresponding quote in the first row of Table 4.14 (“35678”) was for 70 hours of service, which is the estimate for fixing the PCs.
Table 4.15 Quote Item
The quoted items for paper and pens in rows 2 and 3 shows more specific products than were asked for in the corresponding request items from Request id “38948” from Table 4.12. Only two of the four items that were requested in request “38948” were quoted.
The QUOTE ITEM may also be associated with an ORDER if the quote is accepted. An ORDER ITEM will generally not have more than one QUOTE ITEM associated with it. The quote item may be the basis for the pricing on several orders; therefore, several ORDER ITEMs may be associated with one QUOTE ITEM.
Similar to orders, both quotes and quote items may have terms associated with them. A quote to fix PCs may have a term stating that someone within the organization needing the repair must act as a central point of contact. The term may specify that if this central point of contact does not exist, the quoted price may be raised. A term of a particular quote item on furniture items may be that the receiving party needs to pay the actual freight costs incurred.
The relationship from QUOTE to QUOTE TERM allows many terms to be associated with the quote. The QUOTE ITEM may also have many terms associated with it via the relationship to QUOTE TERM. The TERM TYPE identifies the possible terms that can be applied. This entity is also used for other types of transactions such as orders.
An agreement is a set of terms and conditions that govern the relationship between two parties—for example, a customer and a supplier. A key difference between an order and an agreement is that an order is a one-time commitment to buy products while an agreement specifies how the two parties will conduct business over time.
Figure 4.12 shows the data model associated with agreements. Agreements are classified into AGREEMENT TYPE—for instance, PRODUCT AGREEMENTs, EMPLOYMENT AGREEMENTs, or OTHER AGREEMENTs, depending on the needs of the enterprise. The AGREEMENT TYPE allows maintenance of many more specific types of agreements. AGREEMENTs may involve many AGREEMENT ROLEs of PARTYs such as AGREEMENT ROLES TYPEs of “seller,” “buyer,” “licensee,” “licensor,” “contracting firm,” and “employer,” and the roles will depend on the type of agreement involved. The AGREEMENT may be associated with a specific PARTY RELATIONSHIP such as a sales agreement that is associated with a customer relationship.
Figure 4.12 Agreement definition.
ADDENDUMs modify the agreement to which they may be attached. Each AGREEMENT may be composed of one or more AGREEMENT ITEMs that, in turn, may be related to a specific PRODUCT. Both AGREEMENT and AGREEMENT ITEM may have terms associated with them and are thus further defined by one or more AGREEMENT TERMs or AGREEMENT ITEM TERMs.
The agreement roles may be defined using either the relationship to PARTY RELATIONSHIP or the AGREEMENT ROLEs or both. For example, there may exist a customer relationship that, when formalized, is done so through an agreement. Each PARTY RELATIONSHIP may be involved in one or more AGREEMENTs. There also may be more than two parties involved in the agreement, such as in a three-way partnership agreement. The AGREEMENT ROLE entity would be used to store the roles associated with the agreement in this circumstance. The AGREEMENT ROLE may also store many other roles such as “legal council,” “approver,” “guarantor,” “entered by,” and any other role involving parties in the agreement.
There are many different types of agreements, and these will vary substantially depending on the nature of the organization. Often many types of agreements are involved in the enterprise, and most of the chapters in Volume 2 illustrate various standard types of agreements that are used in different industries.
Figure 4.12 provides a few standard types of agreements, and Table 4.16 provides sample data for some of these agreements. PRODUCT AGREEMENTs define the terms and conditions of the buying and selling of products. A PRODUCT AGREEMENT may be subtyped as either a SALES AGREEMENT or a PURCHASE AGREEMENT. Agreement 10002 in Table 4.16 is a SALES AGREEMENT. ABC Corporation acts in the “supplier” agreement role and ACME Company acts in the “customer” agreement role. The agreement represents a contractual commitment and is associated with their party relationship of customer relationship, which was an informal relationship.
Table 4.16 Agreement
The EMPLOYMENT AGREEMENT subtype defines the arrangement behind an employee and employer relationship. Agreement 86749 in Table 4.16 is an employment agreement. Over time there could be several employment agreements; this represents just one of them.
The third agreement shown is a PARTNERSHIP AGREEMENT and is between three parties all playing the same role as a “partner.” The data model supports as many roles necessary for any agreement and allows new role types to be added and hence is a very flexible model.
There may be other types of agreements depending on the enterprise involved. For example, there may be ownership agreements, real estate agreements, franchising agreements, licensing agreements, and so on. The AGREEMENT TYPE maintains the various types of agreements that are valid for the enterprise.
One may argue that redundant data is stored in this data model because the roles are stored in the PARTY RELATIONSHIP as well as the AGREEMENT. For instance, in the CUSTOMER RELATIONSHIP, which is a subtype of PARTY RELATIONSHIP, ABC Corporation has a role of internal organization and ACME Corporation has a role of customer. When a formal agreement is established between these parties and a contract is drawn stipulating certain terms of this agreement, an AGREEMENT instance is created, and the AGREEMENT ROLE entity stores ABC Corporation as a supplier and ACME Corporation as a customer.
Doesn't the model already have ACME Corporation as a customer? One answer to this is that there is a difference between a party's role in an informal relationship and in a formal agreement or contract. The informal relationship may exist, or the agreement may exist, or both may exist. Therefore, this model provides a mechanism to store roles involved in any of these cases.
One may also argue that the AGREEMENT can inherit the roles from the associated PARTY RELATIONSHIP. This could work in many circumstances and is perhaps a valid alternative. Agreements may involve more than two parties, though, and it is probably cleaner to store all the roles for agreements in the same place, namely, the AGREEMENT ROLE entity.
An agreement defines a formal contract between two or more parties whereby they both make a commitment to terms for a certain length of time. Each AGREEMENT may have AGREEMENT ITEMs that allow for either subagree-ments or portions of the agreement. These portions may be particular sections or clauses and may allow the agreement to be subdivided and referred to by section. Alternatively, the agreement may have portions that apply to certain geographic areas, certain types of products, or certain parts of the organization involved in the agreement.
Figure 4.13 shows a data model that shows the information requirements needed for AGREEMENT ITEMs. Each AGREEMENT ITEM represents a component of an agreement and has agreement text and optionally an agreement image that could represent a graphic component. For simple agreements, such as a one-page agreement between parties, there may not be a need to store AGREEMENT ITEMs because the text of the agreement may be stored in the AGREEMENT entity text attribute. Standard terms could be attached to the agreement that will be shown in the next figure, which stores the text and terms of the agreement. For more complex agreements, there may be many AGREEMENT ITEMs that represent portions of the agreement.
Figure 4.13 Agreement item.
The AGREEMENT ITEM has subtypes of SUB AGREEMENT, AGREEMENT SECTION, AGREEMENT PRICING PROGRAM, and AGREEMENT EXHIBIT. A SUB AGREEMENT represents an agreement within an agreement, such as a confidentiality agreement that forms part of an overall professional services agreement or several subagreements for different parts of an organization. An AGREEMENT SECTION represents a portion of an agreement that is split out as an AGREEMENT ITEM, allowing it to be related to a specific organization, product, or geographic area if needed. The AGREEMENT PRICING PROGRAM represents information about agreed-on prices for products or features based on different criteria. An AGREEMENT EXHIBIT is a portion of an agreement that is attached to an agreement and usually referred to throughout the agreement.
Each AGREEMENT ITEM may be comprised of other AGREEMENT ITEMs, as shown by the recursive relationship on AGREEMENT ITEM. Subagreements may be broken down into agreement sections, which may be further broken down into agreement clauses. The recursive relationship allows agreement items to be composed in as many levels of detail as necessary.
Either an AGREEMENT or an AGREEMENT ITEM may be modified by an ADDENDUM that could modify the text of the AGREEMENT or AGREEMENT ITEM with the addendum text attribute within the ADDENDUM. The ADDENDUM entity maintains the addendum creation date, which is when the addendum was created, and an addendum effective date, which is when the addendum goes into effect. The addendum text describes the addendum. An example of an addendum is a time extension to the thru date of a customer-vendor agreement. In this case, the thru date would be updated in the AGREEMENT entity, and an ADDENDUM would be added to show the history of changes to the applicable agreement item. Each AGREEMENT may be modified by one or more ADDENDUMs.
Each AGREEMENT ITEM may be specified for certain geographic boundaries, products, and/or organizations. The AGREEMENT GEOGRAPHICAL APPLICABILITY, AGREEMENT PRODUCT APPLICABILITY, and AGREEMENT ORGANIZATION APPLICABILITY allow AGREEMENT ITEMs to be customized for certain geographic boundaries, products, or organizations. For instance, perhaps different subsidiaries within an enterprise may have specific parts of the agreement that apply to each subsidiary. This structure allows separate sections, which can be applied to each subsidiary. Similarly, certain sections of an agreement may be applicable only to certain products or geographic areas.. There may be other factors that define the AGREEMENT ITEM, such as the applicability for specific types of product, and therefore other “applicability” entities could be added, such as, in this case, an AGREEMENT PRODUCT CATEGORY APPLICABILITY.
Table 4.17 shows a simple example that agreement 10002 is an agreement that has two agreement items governing the terms for buying two products: “Johnson fine grade 8½ by 11 blue bond paper” and “Jerry's box of 3½-inch diskettes.”
Table 4.17 Agreement Items
| AGREEMENT ID | AGREEMENT ITEM SEQ ID | PRODUCT |
| 10002 | 1 | Johnson fine grade 8½ by 11 blue bond paper |
| 2 | Jerry'fs box of 3½ inch diskettes |
An agreement and its items may each have terms associated with them. The AGREEMENT TERM entity stores the valid terms of the agreement as well as the effective dates of the terms. Different types of terms can be referenced from the TERM TYPE entity. The AGREEMENT TERM entity provides a structure to maintain various types of terms that could either be applied to the AGREEMENT (for simpler agreements without items) or to the AGREEMENT ITEM. Terms and conditions can include legal terms, financial terms, incentives, thresholds, clauses for renewals, agreement termination, indemnification, noncompetition, and provisions for exclusive relationships.
For example, a product agreement between a supplier and its customer may call for an exclusive arrangement preventing the supplier from selling its products to the customer's competitors. An example of this exclusive arrangement is found when a consulting firm performs services for its client that involve sensitive proprietary information that needs to be safeguarded from the competition. Therefore, the customer may request that the supplier not do business with the customer's competitors for a specified time in order to protect its confidentiality. In Table 4.18, the first row of data gives an example of an AGREEMENT TERM that is associated with agreement 23884. The term is that the agreement calls for an exclusive arrangement prohibiting the supplier from supplying similar services to the customer's competitors for up to 60 days after the contract ends.
Table 4.18 Agreement Terms
Each agreement item may have terms associated with it. The AGREEMENT TERM entity may also maintain the valid terms of an agreement item. The second, third, and fourth rows of Table 4.18 show examples of agreement items and the terms associated with them. The second and third rows (agreement ID #10002, item #1) represent the same AGREEMENT ITEM for the product “Johnson fine grade 8½ by 11 blue bond paper.” There is more than one term associated with this agreement item. The two terms specify that the agreement item is valid only for up to 1,000 of these items between January 1, 2002 and December 31, 2002. The agreement also has a term that the dollars expended cannot exceed $10,000 from January 1, 2002 through June 30, 2002. The fourth row (agreement ID #10002, item #2) specifies that the customer is agreeing to buy these types of products exclusively from the supplier.
Figure 4.14 Agreement terms.
The entity AGREEMENT ITEM relates to an entity that maintains agreed-on product prices between parties. The prices may vary by time periods, quantities, party addresses, and geographic regions.
Figure 4.15 illustrates the use of the pricing model discussed in Chapter 3 (see Figure 3.7) for agreements. In order to handle the needs of agreement pricing, the PRICE COMPONENTs are now additionally related to an AGREEMENT PRICING PROGRAM (subtype of AGREEMENT ITEM). Each AGREEMENT PRICING PROGRAM may have many PRICE COMPONENTs.
Figure 4.15 Agreement pricing.
As already discussed, these PRICE COMPONENTs may be a base price (BASE PRICE), a discount (DISCOUNT COMPONENT), a surcharge (SURCHARGE COMPONENT), or a MANUFACTURER SUGGESTED PRICE. The attribute price is used to record base prices, flat discounts, or flat surcharges. The attribute percent is used to record percentages for discounts or surcharges to certain agreement items. The relationships to GEOGRAPHIC BOUNDARY, PRODUCT CATEGORY, QUANTITY BREAK, ORDER VALUE, and/or SALE TYPE allow different prices based on different combinations of these factors.
An alternative data model is to have a PRODUCT PRICE COMPONENT entity and a separate AGREEMENT PRICE COMPONENT entity. The disadvantage to this alternate model is that there is reuse of a very similar entity with similar attributes. The advantage is that the data structures to price products and agreements are slightly different and that the information in these data structures will probably be used in very different circumstances. One slight difference in these structures is that product pricing may be based on the PARTY TYPE entity (this relationship is shown in Figure 3.7 Product pricing) ; this is not necessary in agreement pricing because the pricing is for a particular party and will therefore not vary by the type of party.
Table 4.19 gives some examples of the pricing components used within agreements. The three rows are the pricing arrangement for agreement 10002 item 1, which refers to the product “Johnson fine grade 8½ by 11 blue bond paper.” The agreement says that the price in the eastern region for this product is $7.00 and in the western region the price is $7.50. If more than 1,000 reams are ordered in the same order, then a 2 percent discount is applied to this product.
Table 4.19 Agreement Item Price
With the addition of this pricing model application, pricing may be determined by three different means: through the standard price associated with a product, through an agreement made in advance, or through a specific negotiation of an order. It is important to specify the business rules that govern when to use which price. Most enterprises have business rules in place that an agreement will override the standard product price and that specific negotiation on an order will overrule standard product prices or agreements.
In most cases, there is no direct relationship between ORDER and AGREEMENT because the terms and pricing of an agreement will dictate business rules that govern the processing of an order.
The data model in Figure 4.16 shows ORDER and AGREEMENT data structures regarding pricing. These data structures have already been shown in separate diagrams and Figure 4.16 provides another view to these structures to provide insight about the relationship from ORDER to AGREEMENT.
Figure 4.16 Relationship of agreement to order.
As illustrated in Figure 4.16, each ORDER has ORDER ITEMs with an associated PRODUCT and optionally with PRODUCT FEATUREs specified. The AGREEMENT has AGREEMENT ITEMs, which may be an AGREEMENT PRICING PROGRAM that has PRICE COMPONENTs dictating the pricing of the PRODUCTs and PRODUCT FEATUREs. The order processing routines would check to see if one or more agreements were in effect by comparing the order date to the agreement from date and thru date as well as seeing if the party ordering the products is the same as the party that had this agreement in place. The ORDER ROLEs and AGREEMENT ROLEs (not shown in the diagram) would need be compared to accomplish this. The AGREEMENT or AGREEMENT ITEM may also have terms associated with it that may be checked at the time of order processing.
The fact that orders are governed by agreements means that the order is subject to the pricing and terms of the agreement. Of course, it is possible for an order to override the pricing and terms of an agreement in some circumstances. For example, perhaps there is an agreement between a customer and a supplier that a certain type of pen will cost $2.00 each. All orders generally follow the terms of this agreement; however, the supplier might make an exception and discount the price of pens by $.50 for a specific order due to a complaint about the late delivery of a previous order. This may be a gesture on the part of the supplier, even though there are no terms or conditions to give this discount.
The data model from this chapter provides a way to maintain information about orders, requirements, requests, quotes, and agreements (see Figure 4.17). These models incorporate both sales and purchase order perspectives and cover services as well as goods.
Figure 4.17 Overall order model.
Orders go through a process, beginning with a requirement or a need for a product(s). The requirement may be directly fulfilled by an order, or it may lead to a request to suppliers, a quote, then an order. Agreements may be established in advance that govern relationships and transactions between parties and influence terms and pricing. This chapter models most of the information that many enterprises need to establish commitments between parties.
Models for delivering these commitments are covered in Chapters 5 and 6. The delivery of commitments of goods is usually accomplished through shipments, which are covered in Chapter 5. The delivery of services is usually accomplished through work efforts, which are covered in Chapter 6.
Please refer to Appendix A for a listing of entities and attributes. SQL scripts to build tables and columns derived from the logical models in this book can be found on the full-blown CD-ROM, which is licensed separately.