Search code examples
databasestored-proceduressql-server-data-toolsmerge-conflict-resolution

Reducing conflicts on stored procedure that changes a lot

I have a stored procedure called "populateProcessTypes" that looks something like this

Insert Into ProcessTypes(processTypeId, processCode, processName)
Select processTypeId, processCode, processName
From
(
Select 1 processTypeId, 'L_EMP' processCode, 'Load Employees' processName UNION
Select 2 processTypeId, 'L_CC' processCode, 'Load Cost Centres' processName UNION
Select 3 processTypeId, 'L_SUP' processCode, 'Load Supervisors' processName UNION
Select 4 processTypeId, 'R_CHK' processCode, 'Run Validation Checks' processName UNION
Select 5 processTypeId, 'R_CLN' processCode, 'Run Data Checks' processName
)

Due to an increased number of developers creating new processes in their own dev environments we often get conflicts or important changes overwritten when merging into the repository. For example, one developer will add 

Select 6 processTypeId, 'R_NEW' processCode, 'Run New Process 1' processName

and another will add

Select 6 processTypeId, 'R_OTH' processCode, 'Run Other Process' processName

and another might rename a process:

Select 2 processTypeId, 'L_CC' processCode, 'Load Cost Centre Hierarchy' processName

I have never been entirely happy with the way data was being loaded into the ProcessTypes table but there weren't any issues when we had only one developer responsible for it. With the current approach developers end up spending way too long checking with each other what should be in the database and what shouldn't or they are just careless and stuff gets overwritten.

Is there a better method to insert the required records into the ProcessTypes table that will resolve in less conflicts and overwrites?

One thing I can think of is to have a procedure for every Process type that just inserts a single record but I'm sure there is a better solution out there.

We use SSDT to manage the database schema

Solution

  • We have a similar problem, we have a table where we need to change quite a lot and other developers changes are not compatible. The key is to get changes into the main dev branch as soon as possible - I don't know what your branching strategy is but we have this process:

    • 1 - Create a branch from dev
    • 2 - Do work, check in on branch
    • 2a - Merge from dev
    • 3 - Merge to dev (after code review etc)
    • 4 - Merge to release (after test etc)

    When we keep the time between 1 and 3 as short as possible and we tell the other devs we are modifying the table we don't see any issues.

    2a is interesting, the longer your branch is, the more likely it is you will get conflicts but if you regularly merge from dev to your own branch the easier it is to merge the changes.

    Instead of a proc we use a merge statement on a post deploy build but the code isn't the problem, it is the way of working :)

    If for some reason you can't merge your changes easily I have seen things like give each developer a range of numbers they can use so dev a has 1-1000, 2 has 2000-2999 etc but this doesn't scale to lots of developers.

    Ed