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:
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.
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:
Code:
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.