Custom data modeling for document forwarding process

Data
It is impossible to develop a more complicated enterprise multiuser management Information System (MIS) without involving the transfer and approval of data files among multiple users in the system. Because the enterprise's demand always changes with the time lapse, plus each enterprise internal office flow is different, a set of general better management Information system should allow system administrator to define the process of document forwarding.

Although the author does not have the opportunity in the developed MIS to implement the document forwarding process customization function, but, early in 2002 has thought deeply about this aspect design. At that time for some reason can not open their own design ideas, now on the market has a lot of mis products to provide such functions, the author has left, so it is time to put my design ideas sorted out, and you share.

First, let's analyze the needs and set goals.

1 under normal circumstances, the enterprise's official documents forwarding, approval is to be transferred by department or position, that is, to hillock not people. For example: a part of a process requires the financial controller for approval, the future financial controller substitution, the process should not be affected. Also, a link in the process may be approved by any one of the departments, or all of the department's personnel will be required to approve it.
2 in the process of transfer, approval of documents generally divided into documents and forms 2 kinds of formats. File format documents should support batch processing, that is, you can forward multiple files at a time, approval can only return one of the unqualified documents, other documents can be transferred to the next link to continue processing. Documents in form format should allow users to define form formats and determine the table entries in the form. In the same vein, the form should support batching.
3 The process of handling official documents should be able to allow users to define their own. This way, once the new processing action is added, there is no need to modify the underlying data modeling of the MIS system. Of course, to implement the new processing action, it is necessary to write the code at the business logic layer, but the workload is much less than the modification of the underlying data model.
4 The number of links per process is not necessarily the same, should allow users to set the number of links, specify the flow of documents in each link in the sending department and accept departments, processing mode, the longest waiting time.
5 When the documents to be processed, the system should be in the waiting time periodically to the next link in the process of the users (people) issued a notice to remind the user (we) timely processing, until the document has been processed. If the maximum wait time is exceeded, the document has not been processed by the user, and the process fails. Enterprise management may require that information be recorded for reference in future business process reengineering (BPR).
6 Some enterprises for special reasons, in a process requirements for the implementation of trans-link processing. For example, the process has 6 steps, the implementation of the second link when required to skip the middle of the three links, directly to the last link waiting for processing. In fact, in this case, it is not necessarily a technical level to achieve its flexibility, which is, after all, a minority. Users only need to define a new process, the above process of the first 1,2,6 step replication to join in, 2 processes between the process name to distinguish between. An excellent system architect should make full use of existing tools and do not set up development on its own.

The above requirements for flexibility, higher levels of abstraction, so in the performance layer and business logic layer of development, the initial investment more, but after the development of the estimate does not need to modify the underlying database, you can meet the future changes in the official document flow needs. If such flexibility is not required, certain assumptions can be simplified in terms of actual projects. The following requirements are followed for use cases analysis and data modeling.

1 due to the process of the sender and receiver is not to the people, we should first draw the entire enterprise setup, determine the rights and responsibilities of each department. There may be a number of sub departments within the larger department, each with a different position in the department responsible for handling the corresponding transaction. So, you can create a tree-relational datasheet to hold the enterprise structure, and then use a combination of permission lists and user groups to preserve the functions of each position in each department. The idea of this piece of design before I released the "Simple discussion of database design techniques (upper), (next)", I directly below the approximate data table structure:

Departmental table (department_table)
Description of name type constraint condition
dp_id int no duplicate category ID, primary key
Dp_name varchar (50) is not allowed to be an empty type name, duplicate is not allowed
Dp_father int is not allowed to identify the parent class of this class, if the top node is set to a unique value
The Dp_layer varchar (6) is limited to 3 layers, the initial value is 000000 categories of first-order traversal, mainly to reduce the number of database retrieval

function table (function_table)
Description of name type constraint condition
f_id int without duplicate function identification, PRIMARY key
F_name varchar (20) does not allow empty feature names, duplicates are not allowed
F_desc varchar (50) allows for empty function description

User Group table (user_group)
Description of name type constraint condition
group_id int no duplicate user group ID, primary key
Group_name varchar (20) not allowed for empty user group name
Group_power varchar (100) is not allowed for empty User Group permission table, the content is the collection of function table f_id

User table (user_table)
Description of name type constraint condition
user_id int no duplicate user ID, primary key
User_name varchar (20) No duplicate user name
User_pwd varchar (20) is not allowed for null user password
User_type int is not allowed for null-owning user group identity, and USER_GROUP.GROUP_ID Association

Description: A user group that sets different permissions for different positions in the department, such as "marketing salesman" for a user group, and the user group can send claims in the process "claim".

2 Although the documents in the process are divided into file and form 2 formats, each file/form should have its unique identification, name and other attributes. Therefore, we abstract the document, the 2 forms of the common attributes of documents extracted to establish a document table.

Official form (document_table)
Description of name type constraint condition
doc_id int no duplicate document identification, primary key
Doc_name varchar (50) not allowed for empty official document name
Doc_type char (1) is not allowed for empty official document type

The Doc_type field is used to identify the document format, there are currently only 2 formats, can be set "1" to represent the file format, "2" represents the form format. It is estimated that the future of the new document format will not be too much, so the field only one character. File format documents are generally fixed in the file format, we can use a binary field directly to save the contents of the entire file. Document format documents need to build a table to hold the relevant information, the approximate data table is as follows:

File Table (file_table)
Description of name type constraint condition
file_id int no duplicate file identification, primary key
file_name varchar (50) is not allowed to empty file names
File_value binary is not allowed for empty file contents
......

Documents in form format to allow users to define form formats themselves, determine the table entries in the form. There are two ways to achieve this:
① whenever a user creates a form in a new format, a new table is created that treats the form entries entered by the user as fields of the table. The advantage of this method is that the form query speed is quick and convenient, and the development of the business logic layer is small. The disadvantage is not very flexible, if the enterprise uses more forms of different forms (>20), the structure of the entire database is more confusing, and most forms have the same field, which also increases data redundancy. The data in this way is modeled as follows:

Form Master Table (sheet_table)
Description of name type constraint condition
sheet_id int no duplicate form identification, primary key
Sheet_name varchar (50) is not allowed for empty form names
table_name varchar (20) is not allowed to list table names for empty tables, such as Sub_table1/sub_table2

Table List 1 (sub_table1)
Description of name type constraint condition
sub_id int no repeating table identification, primary key
Option1 varchar is not allowed for empty form entry 1
Option2 varchar is not allowed for empty form entry 2
Option3 varchar is not allowed for empty form entry 3
......

Table List 2 (sub_table2)
Description of name type constraint condition
sub_id int no repeating table identification, primary key
Option1 varchar is not allowed for empty form entry 1
Option2 varchar is not allowed for empty form entry 2
Option3 varchar is not allowed for empty form entry 3
......

......

② an abstraction of a form, which is viewed as a collection of several form items combined. The advantage of this approach is that the user creates a new format form by simply checking out the required table entries from the existing form entries, and the entire database has a clear structure and no data redundancy. The disadvantage is that the development is more complex, the workload is higher than the above, and the form query speed is slow. Here's how data is modeled in this way:

Form Master Table (sheet_table)
Description of name type constraint condition
sheet_id int no duplicate form identification, primary key
Sheet_name varchar (50) is not allowed for empty form names

Form table Item table (option_table)
Description of name type constraint condition
op_id int no duplicate form table item identification, primary key
Op_name varchar (50) is not allowed for empty form table item names
Op_length int is not allowed for empty form table item length
Op_unit varchar (10) Allow empty form table Item Units

Form Information table (sheetinfo_table)
Description of name type constraint condition
info_id int no duplicate form information ID, primary key
sheet_id int does not allow for null-owned form identification, and SHEET_TABLE.SHEET_ID Association
op_id int is not allowed to be identified as an empty form table item, and OPTION_TABLE.OP_ID Association
Info_value varchar () is not allowed for empty form information values

3 We can abstract the process of document forwarding as an entity super class. The table is as follows:

Process table (flow_table)
Description of name type constraint condition
flow_id int No repeat process identification, primary key
Flow_name varchar (50) is not allowed for empty process names
Flow_stepnum int is not allowed to empty flow steps
Flow_desc varchar (200) allows for empty flow description

Each step in the process can be abstracted into a use case from the sender to the receiver, and its data is modeled roughly as follows:

Process action Table (action_table)
Description of name type constraint condition
a_id int no duplicate action ID, primary key
A_name varchar (20) NULL action name not allowed
A_call varchar (50) does not allow modules that are called for empty actions
A_desc varchar (200) Allow null action description

Note: If the process-oriented development approach, such as a pure scripting language, you can write each processing action as a function, call the A_call field records of functions, can complete the corresponding processing action. If you use the object-oriented development approach, you can encapsulate the processing action with COM components, then a_call the interface method used to record the corresponding COM component. If you are in a. NET framework environment, you can take the form of Web services. Of course, the sender, the receiver, and the document identification are the input parameters.

Process link Table (step_table)
Description of name type constraint condition
step_id int no repeat link identification, primary key
belong int is not allowed for null-owned process identification, and FLOW_TABLE.FLOW_ID Association
Setp_order int is not allowed to empty the step order of the owning process
Sender int does not allow for null sender identity, and USER_GROUP.GROUP_ID Association
Receiver int is not allowed for null receiver identity, and USER_GROUP.GROUP_ID Association
a_id int does not allow null handling of action identities, and action_table.a_id associations
A_type int is not allowed to have the number of processing required for a null receiving party
max_wait int is not allowed to be empty maximum wait time
Wait_unit varchar (5) units not allowed for empty wait time

Description: A_type is used to determine the number of processing required by the receiving party, "0" means that the owner of the job should be handled together, "1" means that only one of the employees of the position to handle, "2" means that the position of any two employees to deal with, in turn ... The way it is handled is related to action, such as voting, a minority obeying the majority, or a person having a veto power and so on. Perhaps for some processing action has to be refined, related data modeling, here I will not be broken down.

4 The following analysis of document forwarding process link records. This is equivalent to instantiating an object in a process link, and the sender and recipient should specifically contact a (some) user of the management information system rather than a user group. Every step of the way, in addition to preserving this information, we must also preserve the information forwarded by the link, as well as the status of processing. Furthermore, the number of documents forwarded by this link is greater than or equal to one, so you can refer to the "Concise batch m:n design" in the "Database design techniques (below)" That I released before, which is roughly as follows:

Process link Record sheet (step_log)
Description of name type constraint condition
log_id int no repeat link record identification, primary key
step_id int is not allowed for null link identification, and STEP_TABLE.STEP_ID Association
Sender varchar (100) is not allowed to send a user identity for null, and a collection of user_table.user_id for related user groups
Receiver varchar (100) is not allowed to accept user identities for NULL, the collection of user_table.user_id for related user groups
doc_id int does not allow null forwarding of official document identities, and document_table.doc_id associations
Batch_no int does not allow for empty bulk forwarding Official document number, the same process link forwarding batch_no the same
State char (1) is not allowed to be null-processed
Sub_date datetime is not allowed for empty commit time
Res_date datetime allows reply time for null processing
Comment varchar (255) Allow reply comments for null processing

Description
① the same process link forwarding batch_no and the batch of the first warehousing log_id the same. For example: Suppose that the current maximum log_id is 64, then a user forwarded 3 documents at a time, then the batch inserted 3 process link record batch_no are 65. Then another user through a process link forwarded a document, and then insert the process link record of the batch_id is 68.
The ②state field is used to describe the state of its process link, is pending processing, has been processed, dismissed, or exceeded the maximum waiting time by the system automatically retracted, and so on. With this field, we can send a notification to the receiving user, and it is easy to query all the processes that exceed the maximum waiting time to be automatically recovered by the system, so that the enterprise management will refer to the business process reengineering (BPR) in future.
③ If an official document is disposed of in a certain part of a process, it can be considered as the official document which is dismissed to the starting point in the process, and the original sender can then resend the document according to the processing reply comment.


Summarize:
Business document process customization should be the enterprise has fixed the document forwarding, approval process electronic, efficient paperless office, for informal oral discussion, deliberation, assembly and other business activities is not suitable. When the enterprise accumulates a certain number of electronic document forwarding records, can be in business consulting experts and technical developers to carry out data mining, analysis of which inefficient, useless links, optimize the reorganization, and ultimately improve the competitiveness of the entire enterprise. As a technical development personnel, we should according to the actual operation of the enterprise, capital investment scale, choose the current period of the most suitable technology solutions, must not to show their technical strength, and the development of complex, enterprise development is not the pursuit of technology the most advanced, and most suitable.