Prototype Product Application

Introduction

Database Schema - Product details

Product Definition

Product Category

Product Identification Codes

Product Features

Units of Measure

Product Search

Price Components

Database Schema - Inventory

Inventory Item

Inventory Item Location

Inventory Item Status

Inventory Item Variance

Lot Tracking

Amendment History

Comments

Introduction

The download of the Radicore framework includes several prototype applications which demonstrate the kind of application which the framework is designed to support. One of these is the PRODUCT application, and the purpose of this article is to provide a description of that application so that you can see what it does as well as how it does it. This may prove useful in the event that you should ever want to build your own version of the prototype.

Database Schema - Product details

This is the database schema used by this application showing all the entities (tables) and the relationships between them.

This schema is based on the one documented in The Data Model Resource Book by Len Silverston.

Figure 1 - Database Schema (Product details)

Product Definition

The product database includes products which the organisation provides. These may be assembled or manufactured internally, or may be obtained from external suppliers. Some of the information may be independent of the supplier, such as the description, category and features of the product. Some of the information may depend on the supplier, such as availability and pricing.

Figure 2 - Product Definition

Product Category

The classification of products is a key aspect of maintaining product information. Products are often classified in many ways - by product line, by model, by product grade, by industry segments, and by various other categories. Instead of giving each of these classifications a separate entity in the data model it is often a better idea to group them together in a single entity as this allows categories to be easily related as supertype and subtype. This allows the targeting of market interest in products based on several product categories, and also allows product pricing to be based on and related to many product categories.

It is also possible for a product category to contain other product categories. For example, the categories "paper" and "pens" can be defined as sub-categories to "office supplies". It is also possible for any category to belong to more than one super-category. For example, "paper" is a sub-class of "office supplies" but may also be a sub-class of "computer supplies".

It is possible to give a product more than one category. For example, diskettes are categorised both as office supplies and computer supplies. When performing various queries by category, such as sales reports, it would be possible for the same product's sales to appear under multiple categories. The mode has a primary flag to indicate which is the primary category for each product to avoid this situation.

• The PRODUCT_CATEGORY table holds all the product categories.
• The PROD_CAT_ROLLUP table shows where a product category is made up of other product categories in a recursive hierarchy.
• The PROD_CAT_CLASS table links a product category to a product.

Figure 3 - Product Category

Figure 4 - Product Category Rollup

Figure 5 - Product Category Classification

Product Identification Codes

Goods may have various ids that are used as a standard means of identifying them. The data model must therefore allow each product to have any number of different identity codes.

• The GOOD_IDENTITY_TYPE table holds the various identity types.
• The GOOD_IDENTIFICATION table holds the various identity values for each product.

Figure 6 - Product Identity Types

Unique keys are:

• PRODUCT_ID and IDENTITY_TYPE_ID
• IDENTITY_TYPE_ID and ID_VALUE

Figure 7 - Product Identification

Product Features

Products may have features that could also be called characteristics, options, variations or modifiers, allowing either customisation of a product or describing the characteristics of a product. These features and options are combined into a single table as what might be a feature to one product could be an option on another. Also, the same product variation may be part of the product one day but later in time be considered as an optional feature.

The data model must therefore be capable of holding details of features that may be required as part of the product, may come standard with the product (meaning that they may be de-selected), or may be selected as an option. It should also be possible to define certain combinations of features as either mutually exclusive or mandatory.

• The PRODUCT_FEATURE_CATEGORY table holds all the categories of product feature.
• The PRODUCT_FEATURE table holds all the product features.
• The PROD_FEATURE_APPLICABILITY table shows which features apply to which products.
• The PROD_FEATURE_INTERACTION table shows how one feature interacts with another.

Figure 8 - Product Feature Category

Figure 9 - Product Feature

Figure 10 - Product Feature Applicability

Figure 11 - Product Feature Interaction

Units of Measure

Although a unit of measure may be considered to be an example of another product feature, in actuality most organisations will conclude that if the same type of product is sold in different units of measure then it is a different product. For example, a ream of paper is quite different from a box which contains 5 reams, and would be regarded as a separate product. This is why UOM_ID is included in the PRODUCT table.

It is important for organisations to be able to determine inventory for products that are identical except for the unit of measure. The UOM_CONVERSION table provides the capability to use a common unit of measure to calculate how much inventory of a product the organisation has. For example, there may be several products for Henry #2 pencils that have different units of measure such as "each", "small box" and "large box". In many cases, organisations need to show total inventories, costs and sales for all of a product regardless of its unit of measure. By defining a common unit of measure such as "each", and including a conversion factor (for example, 12 for "small box" and 24 or "large box") it is possible to determine the total amount of Henry #2 pencils that are in inventory and how many have been sold.

• The UOM_CATEGORY table holds all the categories of units of measure.
• The UNIT_OF_MEASURE table holds all the units of measure.
• The UOM_CONVERSION table provides values which allow conversion from one type of unit to another.

Figure 12 - Unit of Measure Category

Figure 13 - Unit of Measure

Figure 14 - Unit of Measure Conversion

Product Search

Many different options can be applied to each product, so it should be possible to search for products via any of these options. This makes the search a little more complicated as it has to scan several database tables instead of just one, and in the case of PRODUCT_CATEGORY it will also have to navigate down the hierarchy of sub-categories.

Figure 15 - Product Search

Price Components

Every organisation seems to have different mechanisms for pricing its products. Most organisations have several aspects to their product price - the base price, various discounts such as quantity breaks, surcharges such as freight and handling charges, and the manufacturer's recommended price.

Each price component stores a start_date and end_date to indicate the dates for which the entry is valid. Each component also stores either a price or a percentage (such as for discounts), but not both.

Each price component may be based on many variables or combinations of these variables. As well as PRODUCT and PRODUCT_FEATURE there may be others such as:

• PRODUCT_CATEGORY - e.g. to allow a discount of 2% on all paper goods during the month of March.
• GEOGRAPHIC BOUNDARY - e.g. for surcharges when delivering to an area outside the organisation's normal geographic boundary.
• PARTY TYPE - e.g. to allow discounts for certain types of purchaser, such as government departments, charities or minority-owned businesses.
• QUANTITY BREAK - e.g. to allow discounts based on the quantity ordered.
• ORDER VALUE - e.g. to allow discounts on orders above certain values.
• SALE TYPE - e.g. to allow a discount for purchasing over the internet.

Figure 16 - Price Components

Price component details can be accessed from the following:

• Products
• Product Categories
• Product Features

Database Schema - Inventory

The following database schema is based on the one documented in The Data Model Resource Book by Len Silverston.

Figure 17 - Database Schema (Inventory)

Inventory Item

While a GOOD represents a catalogued item or a standard product that can be purchased, an INVENTORY ITEM represents the physical occurrence of a good at a location. The good may be "Johnson fine grade 8½ by 11 bond paper" while the inventory item is 100 reams of this good sitting in the central warehouse.

The inventory item may be serialised, which means that each unit has its own serial number by which it can be tracked, or non-serialised, which means that a group of items has a on-hand quantity which may be split across several locations. Non-serialised items may also be tracked by their LOT number.

Unique keys are:

• PRODUCT_ID and INVENTORY_ITEM_ID
• PRODUCT_ID and SERIAL_NO

Figure 18 - Inventory Items

Inventory Item Location

Each inventory item may be tracked at a facility level, such as a warehouse, or it may be tracked at a more detailed level within a facility such as a container or a bin.

• The FACILITY_TYPE table identifies all the different types of facility that may exist within an organisation.
• The FACILITY table identifies all the organisation's facilities.
• The CONTAINER_TYPE table identifies all the different types of container that may exist within any of the organisation's facilities.
• The CONTAINER table identifies each container that exists within a facility.

Figure 19 - Facility Types

Figure 20 - Facilities

Figure 21 - Container Types

Figure 22 - Containers

Inventory Item Status

Each inventory item has a status which records its current condition, for example "OK", "damaged", "being repaired", et cetera. Inventory items which are serialised may have their status changed to different values over a period of time and these changes will be recorded in a separate HISTORY table. Inventory items which are non-serialised should use inventory item variance instead as this allows different quantities of the same item to be given different status values.

• The INVENTORY_ITEM_STATUS table holds the various status values.
• The INVENTORY_ITEM_STATUS_HIST table holds the history of status changes for each serialised item.

Figure 23 - Inventory Item Status

Figure 24 - Inventory Item Status History

Inventory Item Variance

It is possible that the quantity of a item recorded in the system is different to the actual physical quantity. This difference, either a shrinkage or overage, may be discovered during a stock check or inspection. The variance quantity is the difference between the on-hand quantity of the item and the physical inventory at the time of the inventory date. As each variance record is created the item's on-hand quantity will automatically be adjusted.

• The INVENTORY_VARIANCE_REASON table holds all the possible reasons for a variance.
• The INVENTORY_ITEM_VARIANCE table holds the variance history for each non-serialised item

Figure 25 - Inventory Item Variance Reason

Figure 26 - Inventory Item Variance

Lot Tracking

A concept found in some production-oriented industries is that of the LOT. A lot is simply a grouping of items of the same type generally used to track inventory items back to their source, and is often the result of a production run. Another source of lot information may be from the shipment from suppliers, which would determine the lot. This information is very important in the event that a recall of items is required.

Inventory items can be separately identified by lots because each INVENTORY ITEM may be made up of one and only one LOT. This implies that there may be more than one inventory item of the same item type at a specific location if there is more than one lot involved.

Figure 27 - Lots

Amendment history: