There Is a very recurrent problem in corporations and older T.I professionals that the business logic needs to be in the database , although this discussion is still debated in some places and some people say that there are Benefits that must be considered, the truth is, that over the years, many disadvantages are being discovered with this approach, and being exchanged for more robust solutions.
Common problems with the approach of leaving programming logic in the database
A large portion of the corporations in which I acted in fact use this approach, a factor that irritated me at first, after all there are several problems that I passed with this approach, such as:
- code Versioning was almost nonexistent, unless you have some commented code from a previous version
- Debugging was impossible to be done, because the database does not come set up for you Debugar applications, and and usually are heavy banks to replicate on the Local machine and "Debugar" Procedures and Jobs
- Programming asynchronous, libraries and packages that could help us become unviable technologies in the project, because we can not simply install these packages in the bank
- Advantages of Object orientation, Solid, DDD, Micro services and Cloud Computing become expendable, since we cannot work with classes, so SQL code duplications in queries are very common
These are some of the problems that work with business logic in SQL, all for having a small gain in "Performance", which can sometimes be invalid if the application misresponds to the queries created.
An Inconvenient Truth
The fact is that we can rarely escape this model, there are so many people conditioned with this approach, we have difficulty understanding how to solve these problems within the corporation, because we often need to change the mindset of the biggest Tiers gives the area of T. I to in fact have a significant change. What we have left to at least suffer less in our work of having to deal with it, is to create a structure that enables a healthy evolution of the software and that can be easily replaced, how to create a better structure is what we will assemble here now .
The main problem I see in software that has this problem is how to create a versioning framework for Procedures and Jobs created in your bank. A Simple strategy that I prefer to use is to use a more suitable software to work with programming in Fact, as I work with C#, we will use the Microsoft environment as an example. You will need the following items
- a Visual Studio (I'm using the 2017 community)
- a SqlServer installed with management Studio (I'm using SQL Server Express).
- An Account on GitHub
SQL Management Studio vs. Visual Studio
SQL Management Sutdio is an excellent tool, if you want to use SQL commands on your bank to run queries and commands, this is good for DbMs, if you need to correct, pick up or impute data, but when we are talking about programming in our bank, We have better options, follow the steps of how to do the SQL database versioning, and I will work with the AdventureWorks Database that is on Microsoft GitHub on this < a href = "https::/Github.com/microsoft/sql-server-samples" target = "_blank" rel = "noopener noreferrer" > link . If you want a more detailed tutorial on how to create a Database Project in Visual Studio, I used it as a source link .
- First Install the AdventureWorks OLTP database on your SQL server
- Create A new database project in Visual Studio
Yes i play heartstone haha
- Now link your installed database with the project by right-clicking on the project>>Import>>Database.
Importing the Bank
- Now The folders have been created from the base
- Now Just add your code to version control
Add the database in source code control
- Now only give Commit of this and ready your project will be on GitHub with versioning control.
[Conclusion]: Now your project is versioned, with history for when you change, now your projects will be versied, as DB Masters or programmers will have access to the project and can implement improvements in the project. Further forward we will see how Create a good database structure to better separate the domain and have a better understanding of the bases.