3
I am doing inventory control system, and I would like to know the advantages and disadvantages between two forms of implementation to subtract the number in stock after a sale/output.
1) Carrying out a update direct in the application through script in PHP?
2) Make via trigger in the sales table after insert take quantity of product that was sold and give a update in the product table in the field: estoque = estoque - 1
What are the advantages and disadvantages of each method?
6
You can’t tell which is better. It depends on the architecture of every solution, the experience of the team, the goal, you can tell until it’s taste.
Can you make sure you put it into the application whenever it’s needed? It’s not difficult if the application is built the right way. It is not only possible, but desirable that in every application there is only one place to do the operations and use the established algorithm where you need it. In this way it is possible to create different algorithms to run under different circumstances. This is done with DRY. Particularly prefer in the application.
One way to have something canon is to use the trigger in the database. Not everyone likes it. If I have to do it conditionally, it could be more complicated. Can you guarantee that there will be no situation that the trigger does not apply or should apply differently?
Some people will say that in the database there is a competition control. But it is possible to do this in the application also in one transaction.
This is part of the business rules. You can read more on Business Rules in the Database - what are the advantages and disadvantages?. It’s almost a duplicate.
There’s something wrong with the answer to deserve -1?
4
In general, spreading business logic in different layers of the application is avoided. Imagine that other people will maintain the code, what will happen? The new programmer will have to roll a little to realize that the logic that updates the quantity of stock simply is not in the code layer responsible for changing the stock, is in the data layer.
When I studied software engineering, there was an interesting phrase that was used: 'Data layer is data layer/storage, business is business'. but then I realized it’s not that simple and that making the team maintain the conceptual integrity of a project is really difficult, requires discipline.
In this more specific case, the performance will definitely be something to discuss, since removing the Rigger will cause the application to have to perform a bank access to do the update (or other action) and this is usually slower.
You should look at the key factors that will affect your project and what architectural decisions were set at the beginning so that point maintenance implementations do not destroy your architecture but also do not destroy performance.
An article by Martin Fowler (a classic reference in POO) can help you with topics already discussed, I removed an excerpt from the text that may give you a vision:
"With Both of the first two, the database is used Pretty Much as a Storage Mechanism. All we’ve done is Asked for all the Records from a particular table with some very simple Filtering. SQL is a very very Powerful query language and can do Much more than the simple Filtering that These examples use...."
Something like: in general, the database is a mechanism for storing data, all we do is request a set of data with a particular filter criterion. SQL is a powerful language and can do much more than simply filter ... - free translation.
This text shows the basic definition and enters into the discussion of the major aspects of using logic in the application or in SQL statements, is not so didactic and assumes that the reader has experience in certain scenarios, but, will help.
http://www.martinfowler.com/articles/dblogic.html
There is another however, not so discussed, many commercial applications support more than one database (Oracle, Mysql, SQL Server, etc), the "SQL language" that each supports will vary according to the vendor, as well as features that can be used in conjunction with the language chosen for the other layers (PHP, Asp.net, etc.), so putting the business rules in the bank, will require a more complex product maintenance strategy for various Databases, as the main validations of a software are the business rules and this cost can impact directly on the product management model.
Browser other questions tagged php mysql database software-engineering trigger
You are not signed in. Login or sign up in order to post.
I think my answer was a little indirect, it was not clear to me if you are choosing a general architecture that will be implemented or if you are discussing a particular case to implement a functionality... Like, I will implement Trigger only here in the stock update or I will put the whole business rule in the bank and only this will stay here because it is faster..
– Intruso
You have two answers. You have solved your problem?
– durtto
Have any answers solved what you were looking to know? Do you think you can accept it now? If not, you need something else to be improved?
– Maniero