OFBiz database table structure Design (2)-Contact_mech

OFBiz, the party's telephone, address and other contact methods are very ingenious design, let us carefully analyze it.

There is a table called Contact_mech, this table we call it the Contact table, a telephone number, a mailing address, an e-mail, each will find a corresponding record in this table. Then through the Party_contact_mech table and party for many-to-many association, that is, a party can correspond to multiple contacts, the same contact can also correspond to multiple party (such as family members share a phone number).

In Party_contact_mech, we found the two fields of From_date and thru_date. We can certainly understand that each contact will have a valid period (from From_date to thru_date), so that we don't have to worry about the party's contact way. For example, if a user's phone number changes, we just set the thru_date in the original Party_contact_mech record to today, and then add a record to represent the user's new phone number. This design retains the old phone number, allowing the system operator to always find history.

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, which is a foreign key that points to contact_mech_type. If we import the initial data when initializing the ofbiz, we will find that the Contact_mech_type contains the types of contact such as "email_address", "postal_address", "Telecom_number" and so on. Here someone will ask, how do not mobile_number (mobile phone number)? In OFBiz, the phone's contact type is also "Telecom_number". So how do you express your phone? You need to introduce another table, Contact_mech_purpose_type. In the initialization data, we find a lot of data such as "Phone_home", "Shipping_location", "phone_mobile", etc. representing the purpose of the contact. The cell phone is defined as a connection purpose in the Contact_mech_purpose_type table (i.e. "Phone_mobile"), and is passed through the Contact_mech_type_purpose table (Note not contact_ Mech_purpose_type) has a many-to-many association with CONTACT_MECH_TYPE_ID. Contact_mech_type_purpose also defines which contact types can be linked to.

In the Contact_mech, there is a field called info_string, if the Contact_mech represents an email, then info_string content is email, but if this contact_ Mech represents the phone number or address, where to store the area code, city, zip code and other content? Obviously, OFBiz will not put all this information peremptorily into a string into the info_string, so that there must be two other tables: Telecom_number (storage phone number) and postal_address (storage address), Each of the two tables has a foreign key pointing to Contact_mech.

Here, we have already introduced the party's contact method almost, the basic concept is already in. But there is a table, perhaps the most critical one, Party_contact_mech_purpose. This table is mainly three fields, all foreign keys: party_id (foreign key to party), contact_mech_id (foreign key to Contact_mech), Contact_mech_purpose_type_ ID (the foreign key pointing to Contact_mech_purpose_type). The combination of these three foreign keys uniquely indicates what a party's contact (Contact_mech) is for (Contact_mech_purpose_type).

But this party_contact_mech_purpose and Party_contact_mech feel is coincident, Party_contact_mech_purpose already contains party_contact_ MECH all the information, the reason is to have Party_contact_mech, may also be for conceptual and use convenience, but this also increased maintenance cost.

Original address: http://www.cnblogs.com/jpfss/p/9023024.html

OFBiz database table structure Design (2)-Contact_mech