There are 4 types of subtable
1. Look up flat
2. Look up qualified
3.Look up Hierarchy
4.Look up Taxonomy
A lookup table is used to store values that are shared by many records in other tables, and also to act as a valid table that defines the set of legal values of the corresponding lookup field for data entry and search. For example, a single manufacturer is typically associated with many product records. By storing manufacturer data in a lookup table, you can edit the single copy of a manufacturer’s data to immediately update all records that reference it. At the same time, by defining a lookup field into the manufacturers table, you can constrain the set of legal values of the lookup field to the corresponding set of values for manufacturer in the manufacturers table.
For simple lookup tables, you need to decide whether the lookup values should be stored in a flat table or a hierarchical table, and what subtable fields are needed to store the additional information about each subtable record. For example, the Manufacturer field in the Productstable typically should be a lookup field, with the Manufacturer data stored in a lookup table. Furthermore, the Manufacturers lookup table should be hierarchical if, for example, you need to accommodate company divisions.
Product categories and subcategories, along with the attributes associated with them, are represented in an MDM repository as a hierarchy in a taxonomy table. A taxonomy table is a special kind of lookup table that provides support not only for a hierarchy of category and subcategory records, but also for category-specific attributes that can be assigned to each category on a category-by-category basis.
By convention MDM often refers to this table as the Categories table. Every product should belong to a category.
Whereas the taxonomy table itself and the fields of each of its records are created and defined in the MDM Console, the hierarchy of product categories and their associated category-specific attributes are created and managed using the MDM Client in Taxonomy mode.
A normal flat or hierarchy lookup table is effective for a single multi-valued lookup field when: (1) the lookup table contains a relatively small number of records compared to the main table; and (2) the lookup table records themselves are standard for every main table record and represent a predefined and relatively fixed set of lookup values, such as a lookup into a list of legal manufacturer names.
A qualified table is necessary when the number of lookup table records would otherwise be very large, because each main table record is related not just to the predefined lookup values of the lookup table records but also to one or more additional fields of information that are different for every main table record (such as quantity price breaks, multiple prices for different divisions, regions, or trading partners, or cross-reference part numbers for different distributors or contract customers). In these cases, the fields whose values are different for each main table record should be defined as qualifier fields of the qualified table; the qualified table will then contain an actual record for each of the predefined lookup values or value combinations (such as distributor, contract customer, division, region, or trading partner).
A qualified table used for multiple prices, cross-reference part numbers, or other distributor-specific information usually contains few, if any, lookup fields and multiple qualifiers.
Qualified table records also provide a way to store additional distributor/supplier/customer-specific information for each of multiple distributors/suppliers/customers for each main table record.
In practice, the use of qualifiers and a qualified table instead of normal fields and a subtable keeps the number of actual records in the qualified table very small, but since every link between a main table record and an instance of a qualified table record contains additional information, the number of qualified link table records necessary to store the additional information is very large, often larger than the number of records in the main table itself.
When used for multiple prices or cross reference part numbers, qualified tables and qualifiers allow you to store a massive amount of potentially sparse data, by eliminating n fields from the main table and replacing them with a single qualified lookup field into a qualified table that has n corresponding records and one or more qualifiers. For example, n price fields, one for each distributor or quantity price break (or worse, each distributor/quantity price break combination) can be replaced with n qualified table records, one for each distributor/quantity price combination, and a qualifier for the price.
Consider first the main table of product records shown in the figure below that contains sparse quantity pricing data for each product.
SKU
Name
1-9
1-24
1-49
10-24
25-49
50-99
113
Widget
$3.51
$3.48
$3.44
$3.40
114
Wrench
$8.75
$8.30
$7.99
115
Bearing
$5.12
$4.80
Using a qualifier to store the quantity pricing data, the qualified table would have a single field Quantityand a single qualifier Price, and would contain the quantity records shown in the figure below.
Pricing:
Quantity
[Price]
1-9
1-24
1-49
10-24
25-49
50-99
A qualified lookup field in the main table would replace all of the quantity price fields, and the pricing data would be stored as qualifier values associated with main table/qualified table links, as shown in figure below.
SKU
Name
Lookup [Pricing]
113
Widget
1-9; $3.51
10-24; $3.48
25-49; $.344
50-99; $3.40
114
Wrench
1-24; $8.75
25-49; $8.30
50-99; $7.99
115
Bearing
1-49; $5.12
A main table/qualified table link is created only for those product/quantity combinations for which a price value actually exists.
Now consider the main table of product records shown in the figure below that contains one or more cross-reference part numbers for each product.
SKU
Name
Grainger
McMaster
Applied
Newark
213
Gear
G-408
A4Y-227
215
Sprocket
45-680
MA-215
A4Y-285
Using a qualifier to store the cross-reference part number data, the qualified table would have a single field Distributor and a single qualifier Part No, and would contain the distributor records shown in the following figure.
Part Numbers:
Distributor
[Part No]
Grainger
McMaster
Applied
Newark
A qualified lookup field in the main table would replace all of the distributor cross-reference part number fields, and the part number data would be stored as qualifier values associated with main table/qualified table links, as shown in the following figure.
SKU
Name
Lookup [Part Numbers]
213
Gear
Grainger; G
Newark; A4Y
215
Sprocket
McMaster; 45
Applied; MA
Newark; A4Y
Finally, consider the main table of product records shown in the following figure that contains distributor-specific quantity pricing data for each product.
SKU
Name
Grainger/1
Grainger/10
Applied/1
Applied/25
213
Gear
$3.51
$3.28
$3.49
$2.99
215
Sprocket
$5.01
$4.80
$5.04
$4.81
Using a qualifier to store the distributor-specific pricing data, the qualified table would now have fields Distributorand Quantity and the qualifier Price, and would contain the records shown in the figure below.
Pricing:
Distributor
Quantity
[Price]
Grainger
1
Grainger
10
Applied
1
Applied
25
A qualified lookup field in the main table would replace all of the price fields, and the pricing data would be stored as qualifier values associated with main table/qualified table links, as shown in the next figure.
SKU
Name
Lookup [Pricing]
213
Gear
Grainger; 1; $3.51
Grainger; 10; $4.28
Applied; 1; $3.49
Applied; 25; $2.99
215
Sprocket
Grainger; 1; $5.01
Grainger; 10; $4.80
Applied; 1; $5.04
Applied; 25; $4.81
Each qualified table field that becomes a qualifier reduces the level of validation by reducing the number of qualifier table records and associated set of valid value combinations. For example, in the example above, Price is the only qualifier, so only Distributor/Quantity combinations that exist among the four records of the qualified table are valid. By contrast, if Quantity were also a qualifier, the qualified table would have just two records – one for each Distributor – and the price for any quantity for a valid Distributorwould be valid.
These examples illustrate just a flavor of the power of qualifiers and qualified tables. As you can see, the use of qualifiers offers a great deal of flexibility when it comes to restructuring data for more efficient storage and searching within an MDM repository.
When used to store entire records of distributor-, supplier-, or customer-specific information, qualified tables and qualifiers complement and extend the virtual subset repository capability offered by product masks, allowing the virtual repository associated with each mask to become a custom virtual repository that contains additional custom information for each product record.
<b>A main table field is created in the MDM Console and applies to all of the records in an MDM repository. By contrast, an attribute is created and linked in the MDM Client in Taxonomy mode and applies just to records in categories to which the attribute is linked.</b>
Plz reward points if the answer suffices ur query
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.