4 replies to this topic

[amgab2003]

Hello,
I want to design an application VB.Net / MySQL for inventory management.
On the database I have two tables
Stock (Ref, Quantity))
Movement (Ref, Date, Amount, input / output)
Every time I have an input or output of stock I have to insert the corresponding row in the table movement. The amounts in the table Stock must be updated in accordance with the lines added.

I have two choices (to my knowledge) to design this application:
1 - Create a trigger on the table Movement, which updates the amount of the table Stock
2 - Manage the problem with local functions (in my. Net application)

A second need is to determine is what the stock is "sufficient" or not.
I must have a function that has as input a list of items (Ref, Amount)
and returns a Boolean value to determine if the stock is sufficient or not.

The same concerns: where to place the "business logic"
1 - A function in the database in PL / SQL
2 - A local function in my .Net application (i have to load the entire stock table and performs the verification locally)

What choice you think is best?

Thank you in advance for your advice

[Momerath]

Let the database do the work for you. Loading all the data from a table just so you can filter/validate it is wasteful of bandwidth.

Oh, and this is the C# forum, not the VB.net forum :)

[WingedPanther]

I couldn't disagree with Momerath more vehemently. Do it in the application. Consider this: suppose the requirements change, and there's a new type of movement that shouldn't be logged. Do you really want to update the database so you have a flag that indicates tracked vs untracked changes, then update the trigger so it handles this new flag to determine whether to track data movement? What happens if the requirements change again, and you have two types of logging?

For simple situations, having the database maintain things sounds good, but applications never stay that simple. Worse, suppose your business logic gets split between database triggers and your application. As the business logic gets more complicated, it will become harder and harder to accurately maintain and debug both.

[amgab2003]

Hello,

Thank you for the answer, my major concern is the “coherence” of data. (I’m not sure of the appellation), due to the delivery time of the queries from the .Net application to the database server.

For example, if a customer request a checkout, the cart consists of a list(Reference, Amount), the operation proceeds as follows:

Checkout request
Verification of the availability of items in sufficient quantities
Update the table (The amount values)
The problem appears when a customer check availability and before the update tables is done , the table is modified by another user which will make availability checked above with no mean. This problem appears especially in cases where the business logic is at the client. Because the time t1 and t3 (Case 1) are separated by the time needed to contact the remote database server and retrieve the data. And given that the database is hosted on the internet the interval can be quite large which will make the problem happening often.

This is problem is schematized in the sequence diagram in the attachement (case1).
Yet in the case PL/SQL procedures are used on the server (case2) the interval t2-t1 is relatively short and more you can lock the concerned line(attachement case2).
[ATTACH=CONFIG]4245[/ATTACH][ATTACH=CONFIG]4246[/ATTACH]

[WingedPanther]

Are you using transactions? You could create the checkout record and update the amount values table as a transaction, then verify the amount remaining is non-negative. If it is negative, roll back the transaction and send the user an error message.