Skip to Content
author's profile photo Former Member
Former Member

MDM subtable?

Hey Guys,

I would like to know the different type of subtable with example.

And what is attribute?

~shankar

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2007 at 04:24 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2007 at 09:01 AM
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 04, 2007 at 05:16 AM

    Hi Shankar,

    Basically there are 4 types of subtables:

    1) Lookup Flat-:It is used to store set of fixed values.For eg: in a field country there are fixed set of values so these values can be stored in a flat table.

    2) Lookup Hierarchy-:A hierarchy table organizes information in a hierarchy, where each record is related to a parent record (even if the only parent is the root) and may also be related to sibling records and/or child records. The main table in an MDM repository typically contains some fields whose data may be hierarchical in nature.

    For example, a Manufacturer field may need to accommodate division and subdivision information for manufacturers.

    This hierarchical information is stored in a separate, hierarchy subtable associated with the Manufacturer lookup field in the main table.

    3)Lookup Taxonomy: A taxonomy is the classification scheme that defines the categories and subcategories that apply to a collection of records.A taxonomy table in MDM stores a hierarchy of categories and subcategories and also supports attributes, “subfields” that apply to particular categories rather than to the entire collection of records.

    4)Lookup Qualified-:A qualified table in MDM stores a set of lookup records, and also supports qualifiers, “subfields” that apply not to the qualified table record by itself, but rather to each association of a qualified table record with a main table record.

    for eg:price field may depend on quantity and region.so it may be declared as qualifier field and quantity ,region as non qualifiers

    Regards

    Nisha

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.