OFBiz database table structure design contact_mech

In OFBiz, the contact methods such as the phone number and address of the party are well designed. Let's analyze them carefully.

 

There is a table named contact_mech. This table is called the contact information table, a telephone number, a mailing address, and an email, they will find a corresponding record in this table respectively. Then, use the party_contact_mech table to associate multiple parties. That is, a party can correspond to multiple contact methods, the same contact method can also correspond to multiple parties (for example, family members share a single phone number ).

In party_contact_mech, we found the from_date and thru_date fields. Of course, we can understand that each contact method has a validity period.
(From from_date to thru_date). This design makes it unnecessary for us to worry about the contact information of the party. For example, if a user's phone number has changed, we only need
The thru_date In the first party_contact_mech record is set to today, and a new record is added to indicate the user's new phone number. This design retains the old phone number
So that system O & M personnel can always find historical records.

 

In the contact_mech table, there are two fields: contact_mech_type_id and info_string. Let's take a look at contact_mech_type_id. This field is a foreign key pointing to contact_mech_type. If we import the initial data when initializing OFBiz,
In contact_mech_type, "email_address", "postal_address ",
"Telecom_number" and other contact information types. Someone may ask why there is no mobile_number (mobile phone number )? In OFBiz, mobile phone contact information
Is also "telecom_number ". How can I express my phone number? In this case, you need to introduce another table named contact_mech_purpose_type. In the initialization data, we found many data, such as "phone_home", "shipping_location", and "phone_mobile", representing the contact purpose. The mobile phone is defined in the contact_mech_purpose_type table as a contact purpose (that is, the "phone_mobile" data), and passed the contact_mech_type_purpose table (note that it is not contact_mech_purpose_type) it has many-to-many associations with contact_mech_type_id. Contact_mech_type_purpose defines the types of contact methods and their purpose.

In contact_mech, there is a field called info_string. If contact_mech represents email
The content is email, but if contact_mech represents a telephone number or address, where should we store the area code, city code, zip code, and other content? Obviously, OFBiz won't take these
The information is stored in info_string as a string. In this way, there must be two other tables: telecom_number and
Postal_address (storage address). Each table has a foreign key pointing to contact_mech.

 

At this point, we have introduced the contact information about the party, and the basic concepts are all there. But there is another table, perhaps the most critical one, party_contact_mech_purpose. This table has three main fields: party_id (the foreign key pointing to the Party), contact_mech_id (the foreign key pointing to contact_mech), and struct (the foreign key pointing to contact_mech_purpose_type ). The combination of the three foreign keys uniquely specifies the use of a contact method (contact_mech) of a party (contact_mech_purpose_type ).

 

However, party_contact_mech and party_contact_mech_purpose are overlapped. party_contact_mech contains all information about party_contact_mech, which may also be convenient in concept and usage, however, this also increases the maintenance cost.

 

Favorite for query reproduced from: http://blog.sina.com.cn/s/blog_a2ca5d8c01017m3r.html