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
