Product Availability

An organization sells products in many countries. The logical data model shown in Figure 1 captures which product is available in which country. There are about 100 data elements in the Product entity, only a few of which are shown in this model.

Although certain data elements such as Product Name must be populated for all countries, there are other data elements, such as Product Frozen Indicator, that are only relevant for certain countries. Product Frozen Indicator might be a required data element for Mexico but should never be considered for Canada.

The Challenge

How would you capture the business rule that certain product data elements are only relevant and required for certain countries?

The Response

What makes this challenge tricky is that it cannot be solved by moving the Product attributes to the association entity, Available. If we copy Product Frozen Indicator to Available, we are repeating Product Frozen Indicator for each Country in which the Product is available, and our model is still missing the rule that Product Frozen Indicator is not relevant in Canada, for example. Andrew Knapp, data architect, summarizes this: “It is important to differentiate between the actual values for the attributes for the products versus the fact that a particular attribute is required for a particular country.” We can model that a particular attribute is required for a particular country through the use of indicators or by abstracting.

One solution to this challenge is to add a yes/no indicator for each product attribute to the Country entity. Product Weight Country Relevance Indicator, for example, would contain the value Yes for each Country that considers Product Weight to be relevant. A number of Challengers suggest this solution. The project attributes that are relevant to all countries (e.g., Product Name) will not require indicators in the Country entity. Using indicators is a robust solution as long as there are no new product attributes. However, if Product currently has 100 data elements, the likelihood of expanding to 101 or 105 is very high. Therefore, the use of indicators could lead to a model maintenance nightmare.

Another solution to this challenge is to abstract.We can convert all of the product attributes into attribute instances from a separate entity. We can then relate this very generic entity to Country, and in this relationship capture whether a particular product attribute is relevant for this Country. A number of Challengers, including Jay Dharmapadam, information architect, Georgiana Carlson, business architect, and David Hay, industry expert, recommend this option.

David provides both an explanation and a data model illustrating this solution (see Figure 2,): “First, you have to split the ‘attributes’ out to what I call here PRODUCT PARAMETERs. You could call it PRODUCT CHARACTERISTIC or even PRODUCT ATTRIBUTE.” Emma Fortnum, data architect, calls this entity Product Specification.

David continues: “The point is that this generic entity will contain attributes such as Product Frozen Indicator. As for COUNTRY, you must first decide which PRODUCT PARAMETERS are appropriate for each country. Then, when a PRODUCT is made available in a COUNTRY (subject to an AVAILABILITY), you can retrieve the PRODUCT PARAMETER VALUES of those PRODUCT PARAMETERS for the PRODUCT.”

The product attribute Product Frozen Indicator becomes a value of the NAME attribute in PRODUCT PARAMETER. COUNTRY PARAMETER APPROPRIATENESS captures that Product Frozen Indicator is relevant for Mexico and not for Canada. PRODUCT PARAMETER VALUE contains the value Yes for the Product Frozen Indicator of the Ice Cream Double Chocolate Pop and the value No for the Double Chocolate Bar.

In deciding whether to add indicators or abstract to the logical data model, weigh flexibility against understandability. When moving to the physical, consider the type of application and carefully weigh flexibility against performance and user friendliness. If you would like to become a Design Challenger, please add your email address at www.stevehoberman.com/. If you have a challenge you would like our group to tackle, please email me a description of the scenario at me@stevehoberman.com