Workflow and Permissions
Workflow, and the effective use of workflow, changes drastically from business to business. The important thing to remember when designing workflows is that they have to make people's lives easier. If workflow gets in the way, it will eventually be circumvented or subverted by your user population. Here's a good example: You design and implement a workflow that only allows an author to "route" a document to an editor after a title has been assigned. This is well meaning and based on requirements gathered at the time. However, if a particular author and editor need to collaborate on an article title, and the workflow system won't let the author send the article to the editor, she'll just use email. Part of the point of workflow is to capture that transaction, but you've now lost that ability.
When developing a workflow system for an existing enterprise, it's important to get a feel for how things really work in that enterprise. The documented business process is probably not the whole story. Again, if you impose a workflow on a workgroup, and that workflow doesn't match how people really do their work, your system will be subverted. I like using the UML "activity diagram" to document and design workflows.
Implementing workflow isn't rocket science. Workflow is essentially a finite state machine. A state machine stores the status of a particular item and can act on input to change the status of that item, or can be queried to return the status of an item. A SQL database is a completely suitable mechanism for implementing a state machine. A workflow schema might look something like Figure 2.
Figure 2 Rough schema for representing simple workflows. Each piece of content can be associated with a state or with multiple states.
In the above quasi-ERD, each piece of content links to a ContentState table that stores that item's state information (simply by associating the Content with any of the states stored in the State table).
This schema needs to sit behind a workflow component of your softwarefor instance, a session bean if you're building a J2EE application. This component's responsibility is to enforce the business logic of the workflow rules; for example, "An article can't flow onto the live site until it has been okayed by the legal department." The states in your database should actually represent completion of stages in your workflow. Your business logic can then infer the next stage based on the completion of the previous one ("If it has passed Editorial, then it's time for it to go to Legal").
Versioning and Content Locking
When one author is editing a document, you don't want another author attempting to make changes in the same document at the same time. Content locking, the ability for an editor of a piece of content to "check out" that content so that no changes can be made to it by others, is a key component of content management. Likewise, it's also useful to track changes over time and even be able to "roll back" to previous versions of a piece of content. The basic idea of content locking can be achieved quite easily in a relational databasesimply add a "locked" field to your content item table. Ideally, this field is a reference to an author or user table that allows you to identify who has the item "checked out." That way, if Joe tries to edit the item, he gets a friendly message telling him that Bob has it checked out for editing.