Apache OFbiz member module table structure design, apacheofbiz
In addition to the technical framework, the structure design of database tables can be described as another direction worth learning. In this article, we will talk about the design of ofbiz's e-commerce membership table.
PARTY
Ofbiz abstracts people and groups and translates them into Chinese as "members" (but I think if you leave the field blank, if you also have relevant design requirements, it may be called a group in other fields ). A member is designed as an abstract concept in ofbiz (corresponding to object-oriented design, you can call it a base class). It has two specific extensions (successors ): are PERSON and PARTY_GROUP respectively. E-R diagram of the database:
Here, "PERSON" and "PARTY_GROUP" indicate "individual member" and "organization member" respectively ". Party is just an abstraction that defines the basic features of objects that can be abstracted as "members. However, "individual" and "organizational unit" members have more characteristics than "basic members, therefore, two tables are extended from the Party to store these additional feature information. Their primary keys are the PARTY_ID Of The PARTY table.
PARTY_TYPE
PartyType defines the type constraints of the party. The E-R diagram is as follows:
We can see that PARTY_TYPE has a hierarchical relationship (one of its properties, PARENT_TYPE_ID, is self-associated with the primary key of PARTY_TYPE: PARTY_TYPE_ID, the following if you see a self-associated to itself on the E-R diagram, all represent this relationship, ).
The initial data provided by ofbiz includes the following types:
Shows how to build a hierarchical relationship:
The two tables shown above: PERSON and PARTY_GROUP are also two of them, and these partytypes can be independently extended. PERSON and PARTY_GROUP are only two extensions. This is why the HAS_TABLE value of the two records in the table structure above is Y.
PARTY_ROLE
Just like the "role division" in society, a member must also have his/her own role in the system. The PARTY_ROLE table is used to associate members with role types. It is obvious that the relationship between members and role types is many-to-many (in ofbiz, only the role type is mentioned here, no role, or more accurately, the role type includes the role ).
PARTY_RELATIONSHIP
The members we see above are a kind of "abstract" entities. No matter whether it is an individual or an organization, it will always have a relationship with other Members, as if a person cannot be isolated from society, and he must have his or her own social role, and have contact with other "groups" in the society. This is abstracted as "partyRelationship" in ofbiz ". Let's see if it expresses the semantics of "Link:
When you connect all these fields, it can cover almost all "membership relationships" (you must know that sometimes membership relationships are very complex, A member may sometimes exist in multiple systems ).
Let's look back at the table structure design of PARTY_RELATIONSHIP:
We can see that the first five keys form a joint primary key, and the first four are IDs in the form of XXX_FROM and XXX_TO. Indicates establishing a relationship FROM the "FROM" side TO the "TO" side. PARTY_ID_FROM and ROLE_TYPE_ID_FROM are "Source" and PARTY_ID_TO and ROLE_TYPE_ID_TO are "target.
As shown in the preceding figure, each link has two DATETIME fields, indicating the start date and end date respectively. This indicates that the link has the "time period" attribute. Of course, if there is no deadline, it can be seen as "permanent. Therefore, in order to prevent the relationship from establishing again after the effective period (because the primary key cannot be repeated), the Union "FROM_DATE" is selected as the joint primary key (if the same relationship is established again later, as long as FROM_DATE is different, it is regarded as a new record ).
It is worth noting that in the database design of ofbiz, the "time period" attribute is widely used to identify the validity of records. Compared with logical deletion, this design not only reduces errors caused by foreign key constraints and other associated relationships during deletion, but also directly acts as a "historical record, save the maintenance of the History Table. Of course, its disadvantage is that the records in the table are much more than those in other designs.
Of course, From and To are just To identify the relationship between the two, but they do not show the relationship in the end, just like-I am a friend with you. This sentence can be split into three parts: FROM: me, TO: You, and friends. In the preceding table, a field is used to indicate the relationship: PARTY_RELATIONSHIP_TYPE_ID (this is only a foreign key associated with the table PARTY_RELATIONSHIP_TYPE ).
Create a new link on the interface (this is a link from the external to your own ):
PARTY_RELATIONSHIP_TYPE
This table restricts the type of the link. Such as: Friends, friends, parent, child, Manager, E-R diagram:
It can be seen that the membership relationship type also has a hierarchical relationship. There are two special fields in the table:
- ROLE_TYPE_ID_VALID_FROM
- ROLE_TYPE_ID_VALID_TO
They are used to constrain the role of both sides of the relationship. That is to say, not any two roles can establish a specific membership relationship. Of course, these two fields are usually empty, indicating that they are not restricted.
For each link type, the extension can be implemented independently (the HAS_TABLE field of The Link Type record after expansion is identified as Y; otherwise, the default value is N). In the initialization data of ofbiz, the only extended link type is: employee. Let's take a look at the implementation of the employee relationship table:
As you can see, it is implemented in the same way as the primary key of the previous PARTY_RELATIONSHIP. Therefore, we can regard it as a special implementation of PARTY_RELATIONSHIP_TYPE_ID as the PARTY_RELATIONSHIP of EMPLOYMENT.
Create a link type on the interface:
PARTY_CLASSIFICATION_TYPE for ease of management, ofbiz classifies members by various dimensions. Common Classification types include annual income, value level, industry, and number of employees;
PARTY_CLASSIFICATION_GROUP members do not directly associate with the category type, but with one or more category groups. Classification groups are subject to classification types.
Create a category group:
Relationship Diagram of classification related tables of PARTY_CLASSIFICATION members:
From the association relationship of the table, we can see that the relationship between the member category and the classification group is many-to-many, and the classification is time-sensitive. Therefore, FROM_DATE is used as the foreign key.
Classify members into one membership category:
CONTENT_MECH from this table, we will look at the table structure design related to the contact information of members, which is also a great part of the design.
This table stores the basic contact information. It references another table: CONTENT_MECH_TYPE as the foreign key to indicate the type of the contact method (the usual contact methods include phone number, email address, and website address ).
CONTENT_MECH_TYPE
You can see that the contact type also has a hierarchical structure (parent-child relationship.
To create a new contact method, you must first specify the type of the contact method you want to create:
PARTY_CONTACT_MECH no doubt that the address information can only indicate the Member's address if it is associated with the member. The relationship between members and addresses is many-to-many. When you understand this relationship, you must note that members can be any group, organization, or individual. Two different members may have the same contact information. For example, one employee member and one company Member of the employee may have the same contact information, they can all have the same contact information: The company's mailing address. Of course, it is easy to understand that a member has multiple contact methods. Therefore, there is a many-to-many relationship between the member ID and the contact information ID, and it is similar to the previous design pattern-the contact information is also time-effective, such as changing the phone number or changing the contact information resulting from work changes, therefore, the Union FROM_DATE serves as the Union primary key:
When we select the contact method type as the phone number, the following form is displayed:
If you create a new contact method of the phone number type, where is the phone number stored? The HAS_TABLE field mentioned above is related here (This field also exists in CONTACT_MECH_TYPE ). Under normal circumstances, the contact information is associated with the contact information type. The specific information of Common contact information is stored in the INFO_STRING attribute of CONTACT_MECH. However, some contact information is not simply a string like a mailbox, such as a phone number, zip code, or... They all have specific format representation. Therefore, it is inconvenient to store these special cases with the INFO_STRING attribute. Therefore, you can independently extend the CONTACT_MECH_TYPE (set its HAS_TABLE field to Y, so that the INFO_STRING field is not used when querying the CONTACT_MECH information, instead, use the contact information formatted in the extended table ).
CONTACT_MECH_PURPOSE_TYPE when we click the Save button on the Interface above, the contact information will be further expanded:
In ofbiz, there is another thing called "Contact purpose". What does it mean?
When we see the options, we will understand that there may be many contact methods in a person's address book or phone book. They have no primary or secondary roles, but have different purposes.
PARTY_CONTACT_MECH_PURPOSE: If you have met the contact purpose on the page, you need to associate it with a specific contact information of the member. In this way, a member stores a "Contact Information" record for some purpose.
It is worth noting that it does not directly associate with PARTY_CONTACT_MECH (there is no foreign key relationship). Instead, it copies the three primary keys of PARTY_CONTACT_MECH and combines CONTACT_MECH_PURPOSE_TYPE_ID to form four primary keys, this is because the joint primary key mechanism of PARTY_CONTACT_MECH cannot be referenced by other tables as foreign keys. Therefore, PARTY_CONTACT_MECH_PURPOSE can be considered as an aggregation of the contact information module. How can this be understood? In fact, an address can be regarded as a member (PARTY_ID). For some purpose (CONTACT_MECH_PURPOSE_TYPE_ID), a contact information (CONTACT_MECH_ID) is saved within a certain period of time (FROM_DATE ). The design of this contact method is flexible, so in most cases, this abstraction can cover most application scenarios.
The CONTENT_TYPE member content is designed in a similar way as the contact information. A member can have a file space on the server for saving documents, images, and other things. CONTENT_TYPE specifies the types of content that a member can store:
CONTENT: This table is where the specific CONTENT is stored. Of course it is not unique. If CONTENT_TYPE has a record whose HAS_TABLE value is Y, the table corresponding to the record is also used to store the CONTENT. There are many fields in the content table.
Similar to the previous contact information, a member can have multiple content, and one content may belong to multiple members. Because a member is an abstract concept and may overlap with the entity, a "purpose" is required to modify the member content, which is -- PARTY_CONTENT_TYPE.
PARTY_CONTENT_TYPE is used to modify the usage of the member content. Of course, the table name here is type. In fact, from the data record perspective, it serves as the purpose.
The content is also associated with other tables (mainly referenced, such as CONTENT_ROLE). This is because it does not have much to do with the topic of this article, so I will not explain it again.
To sum up the design of the higher abstract level ofbiz party module, as it applies to scenarios: It is very suitable for the design of membership information related to e-commerce systems. Of course, these table structures are also applied to other related systems in ofbiz, which also means that it is applicable to general industries and systems, thanks to the high abstraction level of this design. It can describe any organization, individual, their address information, and their relationship. In particular, the design of the "relationship" table for Members is very similar to the responsibility model mentioned in the "Analysis Mode:
Therefore, if you are faced with business scenarios with complex organizational structures, such as groups, contacts, individuals, and companies, you can become system users or a very large multinational company: this design can be used in various organizational forms such as headquarters, regional sales offices, offices, and branches.
The inheritance relationships of database tables include PARTY, PARTY_TYPE, PARTY_GROUP, and PERSON tables. We can learn the inheritance design of database tables.
The timeliness design is not true deletion, nor logical deletion, but invalid (FROM_DATE, THUR_DATE ). This method can replace the multi-Table Design of "Operation-operation history" and be merged into an independent table.
