What database structure for a document management system?

The schema for workflows is greatly affected by whether your workflows have branching or not. If all of your workflows are linear with no branching, the problem is much simpler, because the definition of a workflow configuration need only list the steps in the workflow, and a document workflow instance only needs to record the current step. If conditional branching or parallel steps are involved, you need to use some sort of hierarchical model for the steps (eg each workflow configuration step could have a pointer to parents and pointer to children). These are often stored in XML.

The simple case with no branching can look like this:

table: workflow_config (one row per workflow)

workflow_config_id
name
description

table: workflow_config_step (one row for each workflow step)

workflow_config_id
step_id
name
description
action_id
parameters (or context, or whatever information needs to be passed to the action launcher)

table: document_workflow (one row for each document)

document_id
workflow_id
current_step_id
parameters (or context, or whatever information needs to be passed from one step to the next)

table: document_workflow_step (one row for each action taken on the document)

document_id
step_id
status
timestamp

Note that branching workflows need to carry much more information from stage to stage. In fact the "parameters" attribute may need to be in a separate crosslinked table. I'm only calling it a field here because you may use delimited or XML representation if very little data is needed.

answered Sep 12, 2011 at 18:00 dj_segfault dj_segfault 12.3k 4 4 gold badges 31 31 silver badges 37 37 bronze badges

Thanks for ur reply. What must be the structure if i want to use the branching without XML. We just need to save all the workflow in database instead of XML.

Commented Sep 13, 2011 at 15:54

Well, a database field can hold XML, so they're not mutually exclusive. If you need simple parallelism (multiple steps running at the same time), you can use a non-unique step number, and have the workflow fire up each workflow process at the same step at the same time, and not go on to the next step number until all processes at the current step have finished. If you need conditional branching, it all depends on how complex the conditionals are, and whether all conditions are binary or trinary or poly.

Commented Sep 14, 2011 at 14:31

As per our discussion and my understanding i make 4 tables. They are: DocumentTable[DocId][DocName][DocType][DocLocation][UploadedBy][DocStatus] PermissionTable[PerId][DocId][UserId][SequenceNo]SubSequenceNo] CurrentStatusTable[][StatusId][SequenceNo][DocId] And DocHistoryTable[DocHisId][DocId][UserId][Operation][Comment] . These are the tables i am using. So can u tell me if there is any kind of flaw in my structure. The operation is only Accept or Reject with comments.

Commented Sep 16, 2011 at 11:24 I can send you one use case i make according to above database structure. If u give me ur emailId. Commented Sep 16, 2011 at 11:25

You haven't talked about permissions before so I don't know what you're trying to do with that table. I like the idea of a separate table for documenting competed document workflows so the table holding active document workflows stays small. You also really only need the timestamps and end status in it.

Commented Sep 16, 2011 at 15:29

The following is the skeleton of a potential architecture to support your application. It contains definitions of the tables, and columns in the tables, together with some example data for each table. Permitted transactions has two purposes: to generate the requested actions for a particular document in a particular state; and to validate actions when they are appllied to the database.

State Value (PK) Example Data Initial Authenticated Verified Rejected Accepted Flow Flow ID (PK) Description Example Data 27,Authenticate and validate general document Permitted Transition Current State (PK)(FK) Action Type (PK)(FK) Result State (PK)(FK) Flow ID (FK) Example Data Initial,Authenticate,Authenticated Action Requested User (PK)(FK) Document (PK)(FK) Action Type (PK)(FK) Example Data Mr.X,2,Authenticate Document Document ID Current State (FK) Example Data 2,Initial Document Flow Document (FK) Flow (FK) Example Date 2,27 User Name Example Data Mr.X Mr.Y Mr.Z Action Type Value (PK) Example Data Authenticate Verify Reject Accept Action Action Type (PK)(FK) User (PK)(FK) Document (PK)(FK) Example Data Authenticate,Mr.X,2