Sql Server Database Change Workflow Best Practices

SQL Server database change workflow best practices

Version Control and your Database

The root of all things evil is making changes in the UI. SSMS is a DBA tool, not a developer one. Developers must use scripts to do any sort of changes to the database model/schema. Versioning your metadata and having upgrade script from every version N to version N+1 is the only way that is proven to work reliably. It is the solution SQL Server itself deploys to keep track of metadata changes (resource db changes).

Comparison tools like SQL Compare or vsdbcmd and .dbschema files from VS Database projects are just last resorts for shops that fail to do a proper versioned approach. They work in simple scenarios, but I see them all fail spectacularly in serious deployments. One just does not trust a tool to do a change to +5TB table if the tools tries to copy the data...

Best Practices: Storing a workflow state of an item in a database?

If I'm understanding correctly, I would add the BoxItemTasks table (just an enumeration table, right?), then a BoxItemActions table with foreign keys to BoxItems and to BoxItemTasks for what type of task it is. If you want to make it so that a particular task can only be performed once on a particular box item, just make the (Items + Tasks) pair of columns be the primary key of BoxItemActions.

(You laid it out much better than I did, and kudos for correctly interpreting what I was saying. What you wrote is exactly what I was picturing.)

As for determining the current state, you could write a trigger on BoxItemActions that updates a single column BoxItems.LastAction. For concurrent actions, your trigger could just have special cases to decide which action takes recency.

What is an easy way to deploy database changes using SQL Server?

There's various levels of complexity that you can go through:

• if you have update scripts that you create manually, and are just looking for a way to easily apply those to various servers, check out the SSW SQL Deploy by SSW Consulting. It can handle that scenario very nicely

• if you tend to do more of a database diff approach, then Red Gate's SQL Compare (already mentioned) and SQL Packager make a great combo. You can diff the database between old and new and then apply the changes in a nice package - as an EXE or a C# project

• if you want a real, end-to-end, well thought out approach (with a bit of a learning curve), check out Innovartis' DBGhost approach. It's a entire methodology / technique how to handle database development and incremental updates. It's very powerful and look very promising - but it's a bit of an all-or-nothing approach: either you buy into it and use it end-to-end, or you don't

Hope this helps a bit!

Database design for Workflow based system

There are many questions raised by such problems. Ex:

• in your tables, Workflow defines transitions from state to state as changing the assignment from one user to the next.
• this can be an issue. Lets say the user is sick, leaving, on vacation, ... Then your flow is blocked.
• It does not allow for the group concept either.
• others (like I) would define a transitions table. StartState, NextState.
• The workflow would be a list of transitions.
• Each transition requires the user to be of a certain type. Or from a user management point of view, have a certain role, or be member of a group.

If your workflow is fixed and is not subject to change, your method could be ok. But if the workflow is flexible or might be changed / adapted, you should go with something more flexible.

The type of setup you are talking about make me think of the Jira software (form Atlassian), where you define tickets, with status, workflows and users. Is it possible for you to use (i.e purchasse or OpenSource) a workflow management tool? It might be cheaper in the long run than building one.

Your model will potentially be expanded to include:

• clients. Which client is the proposal for?
• who is the sales representative or account manager who is responsible for auditing the workflow and moving it forward?
• link to other systems: how does it link to purchasing, accounts receivable, ...

All this to day, this requires an in-depth analysis which is hard to do on a medium such as this (SO).

---

EDIT: 20181004

I added the following model following your comment. I decided to put the workflow(s) in the database:

Notes (tables in alphabetical order):

• Employee

  • Each employee can be linked to n number of EmployeeRole via the Employee_has_EmployeeRole table.

• Proposal

  • An Employee is linked as the Sales Officer, since he initiates a proposal.
  • A workflow is linked since many workflows could exist for different proposals.

• Transition

  • Linked to State twice. The start state and the end state.
  • An EmployeeRole is linked, to identify which role an employee must have to perform this transition.
  • Enforcing that will be done by the application.

• Workrlow_has_Transition

  • Links Transitions to Workflows.
  • The Employee who completed the transition is recorded here.
  • The date it was done is also kept here.
  • The OrderInWorkflow is just a number that allows you to order Workflow_has_Transition entries.
  • The application will have to make sure a Transition is not done before the others of lower order are done (i.e. DoneDate is null).
  • It will also validate that the Employee trying to complete it has the proper EmployeeRole.

Now, the employee group concept. You can say that a group are employees with the same EmployeeRole. Therefore, when a notification needs to be sent by your application, send it to all users with the required role for the Transition. This avoids you having to create an EmployeeGroup table, which links employees together.

Application scenarios:

- Start a Proposal
    - Verify that the user trying to start a new one has the role "Sales Officer"
    - Collect basic information.
    - Link the Sales Officer to it (current user).
    - Link a Workflow to it.  Only propose the workflows which have at least 1 Workflow_has_Transition.
    - Send a notification to the Employee(s) which have the EmployeeRole for the first Workflow_has_Transition for this new Workflow.
    - These employees receive a notification.
- Progressing through the workflow
    - An employee receives a notification about a Proposal and it's "todo" Transition.
    - Employee views Proposal and Workflow (use the OrderInWorkflow to ORDER BY Transitions).
    - Employee approves if he has the proper EmployeeRole, fill DoneBy_idEmployee and DoneDate.

While going through your application scenarios, you will find gaps or missing items.

Ex.1 do you want to record the rejection of a Transition? How would that be handled then? You send a notification to the employees with the role for that Transition to review it?

Ex.2 do you want to keep the complete history of the proposal? Ex. it Transition X is rejected twice, but approved the third time around.

There are many scenarios like this which will show the weaknesses of your model, which you fix as you complete this analysis. Now it is not perfect, I did not put a lot of time on it. But it is a starting point and illustrates my idea.

---

Related Topics