The structural design of a database table is ofbiz in addition to the technical framework, another very interesting direction to learn. In this article we will talk about the design of ofbiz on e-commerce membership table.
Party
OFBiz to people, groups, called parties, translated into Chinese called "members" (but I think that aside from the field, if you have related design needs, in other areas may be called groups more appropriate). Members in OFBiz are designed as an abstract concept (corresponding to object-oriented design, which you can call a base class), which has two specific extensions (inheritors): Person and Party_group, respectively. E-r diagram of the database:
Here Person,party_group respectively "individual member", "organization member". A party is simply an abstraction that defines the basic characteristics of an object that can be abstracted as a "member". But "individual" and "organization" Members have more characteristics than "basic membership", so there are two tables 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 party's type constraints. The E-r chart is as follows:
As you can see, Party_type is a hierarchical relationship (one of its properties parent_type_id the Party_type's primary key: party_type_id, if you see a self-correlating on the e-r diagram, this relationship is not allocations).
The initial data provided by OFBiz has the following types of party type:
Build into a hierarchical relationship as shown:
The two tables shown above: Person, Party_group is also one of the two partytype, and these partytype can be expanded independently, person, Party_group is also the only two extensions. This is also why the has_table value of these two records in the table structure above is Y.
Party_role
As in the social "role Division", a member in the system will certainly have their own role. While the Party_role table is a relational table for associating members with role types, it is clear that the membership and role types are many-to-many relationships (it is important to mention that there are only role types in ofbiz, no roles, or, more precisely, role types that contain roles).
Party_relationship
The members we see above are a class of "abstract" entities. Whether it represents an individual or an organization, it will always have a relationship with other members, as if a person could not be isolated from the society, he must have his own social role, and with the rest of the community "groups" to create connections. This is abstracted as "partyrelationship" in OFBiz. Let's see if it expresses the semantics of "relationship":
When you connect all of these fields, it almost covers all the "memberships" (you know, sometimes the membership is very complex and a member sometimes exists in multiple systems).
Let's go back and look at Party_relationship's table structure design:
You can see that the first five keys form a federated primary key, where the first four are ID identifiers that are shaped like xxx_from,xxx_to. Indicates that a relationship is established from the "from" side toward the "to" side. Where Party_id_from and Role_type_id_from are "source" parties, party_id_to and role_type_id_to are "target" parties.
As you can see from the diagram above, each relationship has two datetime fields, representing: Start date, due date. This indicates that the relationship has a "time period" attribute. Of course, if there is no due date, it can be regarded as "permanent". Therefore, in order to prevent the relationship from being established again (because the primary key does not allow duplicates), the Union "From_date" is selected as the Federated primary key (later if the same relationship is established again, as long as the from_date is different, it is treated as a new record).
Here it is necessary to explain that in the OFBiz database design, a large number of "time" attribute is used to identify the validity of the record. The advantage of such a design compared to the tombstone is that it reduces the errors caused by the foreign key constraints and so on, but also acts as a "history", eliminating the maintenance of the history table, and of course its disadvantage is that the records in the table will be much more than the other designs.
Of course, from and to is just to identify the relationship between the two, but does not explain how they actually have a relationship, as if-I am friends with you. This sentence can be divided into three parts: from the side: I, to the side: you, the relationship is: friends. The relationship is represented by a field in the table above: party_relationship_type_id (This is just a foreign key, associated with table Party_relationship_type).
Create a new relationship on the interface (here is a relationship from the outside to yourself):
Party_relationship_type
The table constrains the type of relationship. For example: The employer, friend, father, son, manager, e-r Chart:
As you can see, the membership relationship type also has a hierarchical relationship. There are also two special fields in the table:
- Role_type_id_valid_from
- Role_type_id_valid_to
They are used to constrain the relationship between the establishment of both sides of the role. In other words, it is not possible to establish a particular membership relationship between any of the two characters. Of course, these two fields are usually empty, indicating that this is not limited.
For each relationship type, it can be extended to implement the relationship independently (the field has_table is identified as Y, otherwise the default is N), in the initialization data of ofbiz, the only type of relationship that is extended is: EMPLOYMENT. Let's take a look at the implementation of the Employment Relationship table:
As you can see, it is the same way as the primary key implementation of the previous party_relationship. It can therefore be seen as a special implementation of the party_relationship_type_id for employment party_relationship.
Create a relationship type on the interface:
Party_classification_type in order to facilitate the management, ofbiz to members by various dimensions of classification, the common types of classification are: annual income, value grade, industry, the number of employees;
Party_classification_group members do not directly relate to the type of classification, but to one or more classification groups. The classification group is constrained by the classification type.
Create a new category group:
Diagram of the Party_classification member's classification related tables:
It can be seen from the Association of the table, the membership classification and classification group is many-to-many relationship, and the classification has timeliness. Therefore, the joint from_date is used as foreign key.
Classify members into one member Category:
Content_mech from this table, we look at the member's contact information related to the table structure design, which is also part of the very good design.
This table stores basic contact information. It refers to another table: Content_mech_type as a foreign key to indicate the type of contact (usually the type of contact is phone, mailbox, URL, etc.).
Content_mech_type
You can see the contact type, which is also a hierarchy (parent-child relationship).
When we want to create a new contact, we first have to specify the type of contact you want to make:
Party_contact_mech There is no doubt that the address information is only linked to the member to represent the member's address. While membership and address are many-to-many relationships, it is important to understand that a member can be any group, organization or individual. Then there could be two different members who have the same contact possibilities, such as: An employee member and a member of the company to which the employee belongs, they can all have the same contact information: the company's mailing address. Of course, a member has multiple contact information, which is easy to understand. So the membership logo and contact identification is a many-to-many relationship, and similar to the previous design pattern-the contact is also timeliness, such as changing the phone number, change work resulting in contact changes, so the joint from_date as a joint primary key:
When we select the contact type as the phone number, the following form fill appears:
If you create a new contact type for a phone number, where is the phone number stored? This is also related to the Has_table field mentioned earlier (this field exists in Contact_mech_type). Under normal circumstances, contact information is associated with the type of contact, the details of the normal contact is stored in the Contact_mech info_string attribute. But some of the contact information is not pure like a mailbox so just a string, such as phone number, postal code ... They all have a specific format representation. Therefore, these special cases with info_string this property is not convenient to store, so you can independently expand the Contact_mech_type (its has_table field is set to Y, so when querying the Contact_mech information, do not use Info_ String field instead of the formatted contact in the extended table.
Contact_mech_purpose_type when we click on the Save button on the above screen, we will further expand our contact information:
What does it mean in ofbiz that there is something called "Contact purpose"?
See the options we will understand that there may be a lot of contacts in a person's address book or phone book. They have no primary or secondary points, only different purposes.
Party_contact_mech_purpose The purpose of the connection, it is natural that it needs to be associated with a member's specific contact information to be called: a member Stores a "contact" record for some kind of connection purpose.
It is important to note that it is not directly associated with Party_contact_mech (no foreign key relationship), but instead the three primary keys of Party_contact_mech are copied, combined Contact_mech_purpose_type_ The ID forms four composite primary keys because the federated primary key mechanism of the Party_contact_mech cannot be referenced by other tables as foreign keys. As a result, party_contact_mech_purpose can be thought of as an aggregation of contact information modules. How does this come to be understood? In fact, an address can be seen as: a member (PARTY_ID), for some purpose (contact_mech_purpose_type_id), in a certain period of time (From_date), saved a contact method (contact_mech_id). The design of this contact is very resilient, so in most cases this abstraction can cover most of the application scenarios.
Content_Type member content is designed to be similar to the contact method. Members can have a similar file space on the server where they can save documents, pictures, and so on. Content_Type limits the types of content that members can store:
Content The table is where its specific storage is, and of course not unique, if Content_Type has a record with a has_table value of Y, then the table corresponding to that record is also used to store the content. There are so many fields in the table of contents that you can't.
Similar to previous contact information, members can have multiple content, and one content may also belong to multiple members. Because the member is an abstract concept, corresponding to the entity may overlap, so need a "purpose" to decorate the membership content, it is--party_content_type.
Party_content_type is used to modify the purpose of the membership content, of course, here its table is called type, in fact, from the data record, came to serve as a purpose.
The content is also associated with other tables (mainly referenced relationships, such as content_role, etc.), which are no longer allocations because they are not much related to this topic.
Summarize the higher level of Abstraction OFBiz Party module design, as it applies to the scenario: very suitable for e-commerce system member information related design. Of course, many of the other related systems in OFBiz also apply these table structures, which means that they are suitable for general industry and system versatility, thanks to the high level of abstraction in this design. It can describe any organization, individual, their address information, the relationship between them. In particular, the design of the member "relationship" table is very similar to the model of responsibility referred to in the "Analysis mode":
Therefore, if you are faced with a complex organizational structure of the business scenario, such as groups, contacts, individuals, companies can become users of the system, or a very large multinational company, with: Headquarters, regional sales offices, offices, branches and other forms of organization, this design will come in handy.
The inheritance Relation of database table from the table of party, Party_type, Party_group, person we can learn the "inherit" Design of database table.
The timeliness design is not really deleted, nor is it a tombstone, but an expiration (from_date, thur_date). Instead of the multi-table design of operation-operation history, this approach can be merged into a single table.
On the structure design of the module table of Apache OFBiz members
