Task Versioning with Re-submission

• Nicko_Johnston

  Grasshopper

  Points: 21

  More actions

  September 21, 2016 at 11:12 am

  #313810

  Hello!

  I've recently encountered an... interesting problem, and I figured I should get a second opinion/thoughts on a Database design decision.

  I'll start with some background information that may help give context to this problem. We're converting an application which used Sharepoint as its data source into an application that will now use SQL Server as its data source. We are using Entity Framework to retrieve the data from the Database (if that's relevant).

  I'll try to write out a simplified version of the overall current Design (I've used generic names to replace actual table names):

  |Status| (1)---<(n) |Approval|

  |Task| (1)---<(n) |Approval|

  |Task| (1)---<(n) |Documents|

  Task

  ID : int PK

  Name: varchar

  DueDate : Datetime

  CompletionDate : Datetime

  Version: Timestamp

  Document

  ID : int PK

  TaskID : int FK

  Name : Varchar

  AssignedDate : VarBinary

  Approval

  ID : int PK

  ApprovalDate : Datetime

  TaskID: int FK

  StatusID: int FK

  Version: Timestamp

  Status

  ID : int

  Status: Varchar

  The Status Table merely represents whether a task has been marked as complete or incomplete.

  I have a new requirement that asks that I have a version associated with each Task marked "Incomplete", and then allow the Task to be "Resubmitted" with a number that signifies which task re-submission this is. All previous submissions must be accessible for tracking purposes; it needs to be easy to use SSIS to do an ETL operation to track how many times each of these tasks and their re-submissions are submitted late.

  (E.g.)

  First submission should display as:

  Task Name

  Second Submission should display as:

  Task Name (1)

  The N^th submission should display as:

  Task Name (n -1)

  There also must be a link to the Documents which were marked "Incomplete".

  I'm trying to figure out how to best design the Schema to accomplish my task. After discussing it with my colleagues, a few suggestions have come up; but I wanted to get and outside opinion on what to do.

  No matter how we approach this, the following things are likely going to be necessary:

  Add an associative table that links the declined Documents to the Approval Table

  |Approval| (1)---<(n) |IncompleteDocs|

  IncompleteDocs

  Composite Key:

  (ApprovalID: int FK

  DocumentID: int FK)

  My colleagues' thoughts on how to approach this:

  Use Change Data Capture to track the history of the Table/Row to track submission history and retrieve previous versions of the Task, and its due date/submission date. When you need to get previous versions of the Task, go through the history to retrieve the previous versions.

  I have a few worries about this approach. First, I think that's a very complicated way to do this. Second, the approvals would likely have a second column to represent the column version (creating a composite key between TaskID and Version); this would also have the affect of needing to query change data capture to get the previous approval versions as well, which I think would not only make doing the ETL very tricky, but the data access tricky as well. I'm also not sure how to appropriately track how many times it's been resubmitted. (Add a column called Resubmissions as an int?)

  My thoughts on how to approach this:

  Simply create a new entry in the Task table that references the previous entry, adding a column that will allow it to do so. This will also allow there to be one approval for each task (one to one), rather than tying it to the version (which would create a many-to-one relationship).

  The new Schema for that table would be this:

  |Task| (1)---(1) |Task|

  Task

  ID : int PK

  PreviousTaskID: int FK

  Name: varchar

  DueDate : Datetime

  CompletionDate : Datetime

  Version: Timestamp

  Although I'm not sure if I should add a column that represents which resubmission this is or not (Could I do it as a calculated column?). I could do a count of that in code, but it seems at little weird to do it that way.

  What better ways could I accomplish this task? Does mine work well? Is Change Data Capture really the way to go like my colleagues suggest?

  I appreciate any advice/suggestions.