Table references

Many tables include fields, also known as data elements, from other tables. These fields are called referenced data.

The Implemented Software Components (tccom000) table, for example, has references to these tables:

  • Addresses (tccom130)
  • Countries (tcmcs010)
  • Languages (tcmcs046)

In this case, addresses and countries in turn have references to other tables.

Each table reference has these two characteristics:

  • Reference Mode: Describes the relation between the table and the referenced table. The options include the following:
    • Mandatory: The table field must contain a value that is present in the reference table. For example, the reference from Addresses to Countries is mandatory. As a result, each address must have a country, otherwise, the address cannot be saved.

      For table sharing, this implies that if the address table is shared, the country table must also be shared.

    • Mandatory Unless Empty: The table field can be empty. If the table field is filled, the field’s value must be present in the reference table. For example, the reference from Implemented Software Components to Addresses is mandatory unless empty. As a result, the address on the Implemented Software Components table is optional.

      For table sharing, this implies that if the Implemented Software Components table is shared, you can share the address. However, if the address table is not shared, you must leave the address in the Implemented Software Components table empty. Note that the decision not to share the address table can not only be made based on the table references. Often, functionality exists that requires you to fill a field.

    • Not Mandatory: The table field can contain a value that is present in the reference table, but the table field can also contain another value.

      If the reference mode is not mandatory, you need not share the referenced table if the parent table is shared..

  • Type of Reference:
    • Hard Reference: A reference from one table to another table defined in the data dictionary.
    • Soft Reference: A reference from one table to another table that is embedded in a program script or library.

If data is shared, all referenced data might also have to be shared. This can depend on whether the data is mandatory, mandatory unless empty, or non-mandatory. Referenced tables are important in the referential integrity of data.

Where tables are logically linked, the referenced table also must be linked as well, particularly in case of mandatory data. In case of non-mandatory data, fields can have different values in different companies as long as these values exist in the company that references the values. As a result, the main table can be shared using distributed transactions. The referenced table can be company-specific or can be replicated.

Example: General Item Data (tcibd001) is shared by more than one company. Many referenced fields exist in the item file, such as item groups and unit data.

If the item file is logically linked the exact same values for all mandatory referenced fields must exist in both companies, such as item groups and so on. These referenced tables are usually logically linked as well. If non-mandatory fields are not used, in other words, product type, the referenced tables do not must be logically linked.

If a table is replicated, referenced fields can have various values, as long as these values exist in the company that references the values. Then, during the replication, the values change. In the case of non-mandatory fields, you are not required to have the referenced data shared if the field in the table is not used.

Example: If the General Item Data (tcibd001) file is replicated, programs must be able to reference all mandatory fields, in other words, item groups, in the company that uses the data. The value of the mandatory fields does not have to be the same in both companies, as long as the value being referenced exists in the company referencing it. If non-mandatory fields are not used, in other words, product types, you do not have to replicate the referenced tables.

In some cases, many levels of references will exist. The references can be far-reaching and complex. For example, the item file references tables such as item groups and other MCS tables, as well as engineering data, bills of material, routings, and cost accounting data. For these reasons, and because tables can be shared in various ways in each implementation, table references must be investigated in detail for each implementation.