Database Schema Questions (for a queue)

[factotum]

Hi,

This is a long one, but high-level design question, so I could really use your help.

I am developing a web application that enables users to add content to the site. However, depending on roles, their content will have to be approved. I have the following schema:

Table_Pages (general table)
------------------
PK ID
...
 
Table_SpecificPage (specific info)
------------------
FK Table_Pages_ID
...
 
Table_PagesQueue (general table)
------------------
PK ID
...
 
Table_SpecificPageQueue (specific info)
------------------
FK Table_PagesQueue_ID
 ...

When people add content that needs approval, it goes into the queue table and sits (as well as the specific page queue table). Once approved, a sproc checks to see if the id exists in the regular table, and then updates the contents of that row, and then deletes the queue row on success. A cascade will take care of the specific page table row.

When a user edits the page. The existing page (which has already been approved) stays in the page table. However, a row is added to the queue table, which has it's ID as the ID in the regular table. Then, upon approval, it updates the regular table.

Already, you should begin to see the holes in this plan.

For one, each table can create PKs that conflict (and in fact, have been told as much by Sql Server). So, I had a few thoughts.

One, the queue goes negative while the regular goes positive. Have a field in the queue table that holds the id from the live, I'm-being-edited page. Or, create a spin table that holds IDs and state. Or something entirely different? I guess my question is, how best to have the ability to hold existing information about something, and have the ability to edit it (where it may or may not be rejected). I should also add, that the ID can change. I thought about just adding a new row and deleting the old when a page an item is approved. There is other information that gives the page it's context. The ID serves as the unique identifier only.

Thank you.

 

[SQLSister]

First off, the id should never bne allowed to change. THis isasking for trouble.

Now what I would do is if the page is being edited and goes into the queue table, it would go in with the existing ID from the original table. If it is a new page, then it would go in without any data in the id field (you'll have to set it up to allow nulls and not to be unique, what if two people try to edit the page at the same time?). Then I would setup a separate id field for the queue table just in case I need to be able to specifially identify a particular record. Now you should have an approved field and then updates and inserts to the regular table are easily accomplished by query.

Now you have to deal with the problem of what to do if multiple people edit a page differently before the previous edit has been approved. Personally, I would set up the system, so that this is not an allowed action. But if you have to allow it, you will need to consider how to deal with the problem.

Questions about posting. See faq183-874

 

[TJRTech]

Just my own personal bias, but I wouldn't use multiple tables for this.

I understand the rationale for a queue table, but unless you are expecting millions of records, I would just use one table and an indicator column indicating if awaiting approval, a nullable approved_by column, etc.

The reasons for using multiple tables for this sort of thing usually come from a desire to "segregate" the data (queued, for approved, etc). That segregation may be "virtually" obtained by developing SQL VIEWs against the single table, one view per stage of the entries (e.g. vwApprovedImtes, vwUnApprovedItems, etc.)

On big benefit of the approach I describe is that you are NOT moving records from one table to another. A delete from one table and an insert into another should be done in a transaction for data integrity purposes, and transactions create quite a bit of overhead. If everything is in a single table, moving a single item to another workflow state is a simple update...no need to place that in a transaction.

TR

 

[factotum]

Thanks for the two great replies.

I would like to include the ability to have two copies of "pending approval" pages. Therefore, I need to have two tables. Otherwise, I will run out of workstates. In addition, I've kept some things in the Pages table that every page has and could also be edited (for instance, page title). I suppose I could move it out. Perhaps the SpecificPage table could have multiple rows and a field in the Page table points to the "live" row in the Specific page table.

I'm leaning toward SqlSister's solution, as it does allow multiple edits. I appreciate the need for a transaction (which I don't think I have in there now).

Are there any gotchas inherent with this solution? Is there a third option, or should I be good to move in this direction?

Thanks a bunch. I appreciate you guys allowing me to bounce these ideas off of you.

Regards.

 

[TJRTech]

Don't know about other gotchas, but I am still a little unclear on the rationale for multiple tables.

I have done several "workflow" systems and the way I usually model it is (using your problem domain):

1. A Main "Content" table that has columns that describe the content added to your system; one record per each piece of content submitted. This table would have a PK Identity column called ContentID

2. A "ContentState" table that has the static list of content states that define the workflow (aka lifecyle) of content. Typically you would have an PK Identity column called ContentStateID and other columns for things like name, sort_order (aka step_number, for sorting and determing which states come before the others), etc.

3. An associative table that mapps Content to ContentStates, maybe named something inspired like "ContentToContentState". This is an identifying associative table, meaning its Unique PK is a combination of the FKs ContentID and ContentStateID. Additionally, I would add FromDate and ThruDate (nullable) to indicate when the content entered this state, and when it left said state. And you could add a FK to a Person/User table or simply a varchar() column to store who was responsible for moving the content into the associated state (may need two of these, one for the movement in, one for the movement out...not sure if that makes sense).

Anyway, that is the typical workflow model I use. And, I create views against it to get at items in various states and I use those views on different pages on my web app.

TR

 

[SQLSister]

If you want someone to approve where there might have been multiple edits, I would create an approval form which allows them to pick which ID to approve and then calls up the details. Each updateable field will have a combo box the updated data in it. Make it not restricted to the contents of the combo box, so the approver can change the suggested edit as well. Then the approver just has to choose which he or she wants. It would probably be nice to show the orginal data on the form too just above the changed fields. The approval button then replaces the record in the main table with the contents of the form (or inserts a new record) and then deletes the records from the queue table.
The disapprove button, just deletes the records from the queue table.


Questions about posting. See faq183-874

 

[factotum]

TJRTech,

Would you add a new row per edit?

Thanks.

 

[factotum]

(I mean a new row to the Content table per edit). Otherwise, how would you hold the 'pending' state title and the 'approved' state title (or any column data) at the same time?

 

[TJRTech]

What is an edit? Is that the comments? If so, then I ask when can you comment? Can you comment/edit ONLY when moving from one state to another (newly entered -> approved; newly entered -> disapproved, etc)??

If that is the case, then the Comment column would be a field of the ContentToContentState associative table. This allows you to capture the state changes, an associated comment, who caused the state change, and the time entered and time exiting the state.

That's the kind of workflow systems I am familiar with; though what I describe may not suit your needs and if this is taking you off in a tangent I apologize.

TR

 

[factotum]

By edit, I mean that a user wants to change the page title of a page on the web site (or the price of a product, or the name of an employee, etc.). They don't have the permissions to make changes to the live site. Therefore, they edit and then it goes into a different state. I need to hold two states at the same time. One, the page as it exists now (i.e., previously approved) and two, the pending change that hasn't been approved yet.



No worries on the tangent, I just want to get a good schema *before* I fill it.

 

[TJRTech]

Then I would place the edits in the ContentToContentStates table and update the associated Content table columns when things get approved.

Think of ContentStates as ContentEdits if it helps, with one column per each field on a page that may be edited.

I dunno if this helps.

Have you seen Microsoft "Content Management Server" product? It is a little pricey but it does EXACTLY what you describe.

TR