Common Database Design (5) ---- business matching table Design

ArticleDirectory

• 1 Overview
• 2 solutions

1 Overview

There is often a Business Requirement in an application system: for a dynamic basic information table, one or more records need to be identified. For example:

• The cost subject in the accounting system is dynamically added, but we need to remove the fixed asset cost type when calculating some costs.
• For the maintenance of basic unit information, the Unit can also be dynamically added, but our goods must have a basic unit (Public ton), and by default there is a conversionCubeMeter settings.

That is, we need to know which record the fixed assets are in the Cost Table, which record the public ton is in the unit table, and the cubic meter is in the unit table. We can add some identification fields to the original table to solve these requirements. However, in this way, the original design is disrupted for some special needs, and the original design needs to be modified.Code.

2 solutions

To solve this problem, we created a matching table with the following structure:

Note:

• Objtype and objname are primary keys;
• Objtype stores the matching type, such as cost type matching or unit information matching;
• The objname stores the name of the corresponding object. For example, when the basic unit matches, the objname is metrictonne;

Instance data:

In this case, if you want to obtain a base unit of public tons, that is

Select *FromMatchAsMInner JoinUOMAsUOnM. objid=U. uomidWhereM. objtype='Uom'AndM. objname='Metrictonne'