Editing a chain reaction

[newtomysql]

Dear All,
I have a system which first receives item as the hq outlet from then I will do a mark up. Eg. say my cost price is 9.9 so I mark up 0.10. So it becomes 10 when it reaches my first outlet. Thereafter the same product can be transfered from one outlet to the other. The problem comes here is that when I have made a mistake in the markup settings say I set it as 100. So my price becomes 109.90 which is wrong. So I have kept a log to track all the markup changes which I have done. The problem I can track when I transfer from hq to first outlet. Problem here is that from the first outlet I could transfer to second and from second to third and there after. So I dont know how to track it via a chain reaction? Any suggestions please. The database is mysql and language i used is C#. Thanks.

 

[jmeckley]

having difficultly following the chain reaction problem, but it's been a long day however I do believe that tracking changes is a domain concern. not a database, so the fact that your using Mysql is irrelevant.

at a very high level you want to track who made the change when, and what changed. so say you have a member for the item.

public interface product
{
   transfer(ILocation from, ILocation to, Money markup)
}
[code]
where ILocation represents entities like HQ and Outlet. and Money is a value object which encapsulates the price of the item.

within your implementation of transfer you could have logic which creates a tracking entity that contains the user, timestamp original state of product and the new state of product. this collection could then be persisted to a database for retrieval later.

using a temporal pattern you could then retrieve a state in time. when an adjustment is made to correct invalid data a new set of audits will be logged and a series of events could be triggered which automatically update the prices to each outlet sequentially.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newtomysql]

Dear Jason,
I agree is a logic matter and not database. I know I am there is just I can get a proper light to solve it in a smooth manner. Maybe I put for you a scenario then it will make it clearer. So if you notice in the begining the markup is (0.10) which is the correct one so the transfer price becomes 10.00. Upon receiving the average cost price at the outlet 2 is 10.00. Following that there is another transfer from HQ to outlet2 and here there is a mistake done and the price becomes 109.90. Upon receiving at outlet 2 average price is 59.95. Then this is transfered to outlet3. So the transfered price is obivious a wrong one here too. So the worse part it can be furthere transfer from outlet to another outlet. So this is where I am caught exactly. So by looking at my scenario clearly what is your best suggestion. Thank you.

HQ Transfer
(Markup 0.10)
TransferFromID ProductID CostPrice TransferPrice Qty
1 200 9.90 10.00 2

Outlet2 (Do Receciving)
Average Price 10.00
Stock ID ProductID CostPrice
1 200 10.00

HQ Transfer
(Markup 100)#wrong markup
TransferFromID ProductID CostPrice TransferPrice Qty
2 200 9.90 109.90 2

Outlet2 (Do Receciving)
Average Price 59.95
Stock ID ProductID CostPrice
1 200 10.00

Outlet2 Transfer
TransferFromID ProductID CostPrice TransferPrice Qty
2 200 59.95 59.95 2

Outlet3 (Do Receciving)
Average Price 59.95
Stock ID ProductID CostPrice
1 200 59.95

 

[jmeckley]

1st, we are looking at the problem as data (database), not behavior (domain). If we think in terms of behavior, the process may become clearer. data is a point in time, behavior can occur over a period of time.

some questions (from a business perspective, not code):
how is a transfer started?
can a receiver reject a transfer for any reason? if so what reasons?
how is a transfer complete?
how many hours/days before the whole transaction is complete?
how are problem reported to the sender (HQ in this case)?

now from a code perspective:
does your system account for the situations above?
if so, how? if not, that may be a large source of the problem. The system does not reflect the business process.

if the system does reflect the business process then the scenario you described should be handled when the transfer is received. this would mitigate a large portion of the problems.



Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newtomysql]

Dear Jason,
Ok from the business perspective first the items are recieved at the Hq. Then there is markup settings done for each product only for transfer from Hq to other outlet. Inter outlet transfer there is no markup. No a receiver cant reject the transfer. For how the transfer is completed is that first from the particular local database of any outlet we set the item to set status as transfered. Then once received at the outlet it is inserted into the local database of the received outlet. There is no time period lime of how long it is completed but normally in less than 24 hours. Normally if the item has a problem we just re-transfer back to Hq.

From code persepctive
For now each of the transfer is recorded in a table called tblTransfer and tblTransferDetails which keep the details of item linking to one transfer. I keep a copy of my database both in Hq and local outlet. In addition to that for each time there is a change in markup price at Hq I keep a log file which keeps track of changes made to markup price and from this log I can check which transfer are wrongly markup as I keep a data and time and I can know which transfer from Hq are wrongly transfered.

So am I clearer now to you. Any good idea you have to solve my problem.

 

[MichaelRed]

bot to fix the existing screw ups, but for future, add some V&V in the markup procedures, e.g. is 'Markup' in a range? Yoour example (10%) is not in my experience, and seems like it must be specific to a business model.

In typical retail, mark ups are relatively consistient, and are (were) often in the range of 50%.

In any case, you may be able to determine a 'reasonable range' of mark up either on all of the items you deal w/ or even product groups. If so, test for the appropiate range. Even if you cant / wont set a typical range(s), at least do a test on some maximinum?



On the process of fixing the screw up, why not just 'un receive' all items with the screw up, and then re-send / receive with the corrected markup?



MichaelRed

 

[jmeckley]

...first the items are recieved at the Hq.
Then there is markup settings done for each product only for transfer from Hq to other outlet.
Inter outlet transfer there is no markup. No a receiver cant reject the transfer.

ok, good so far

For how the transfer is completed is that first from the particular local database of any outlet we set the item to set status as transfered. Then once received at the outlet it is inserted into the local database of the received outlet.

now we are back to technical details.
what is the people process?
product goes on truck.
the truck goes to the store.
the store gets the product.
then what?

There is no time period lime of how long it is completed but normally in less than 24 hours.

so 24 hours between deliveries, or 24 hours between synchronizing databases, or something else?

Normally if the item has a problem we just re-transfer back to Hq.

ok, so there is a process to reject. you receive it at the destination, then transfer it back to HQ. Incorrect transfer price should be part of this receiving/rejecting process.

MichealRed has the right idea about acceptable markup levels this is one way to reduce data entry errors. But more than that, you want your system to model the business process, not the other way around. So maybe the system needs another intermediate status of "in transit" or "being received" or something were the receiver has some controller over when and how they receive the product.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newtomysql]

Dear Jason,
In fact I have thought the idea of Red cause I have another price called as retail price and I can check transfer price towards it and this can control it.The problem here is that as you know human error can even cause the retail price to be wrong too.

Ok back to the process yes product goes on truck.the truck goes to the store.the store gets the product. I mean at the store they do a receiving of the transfered product. The 24 hours is between transfered and received.

Why I cant reject at receiving end is that the receiving employee cant see the costing either to protect it from them ?

Ok I was thinking a kind of recursive function to search all my transfers from hq to outlet1 to outlet2 etc. Do you think this will work ? Thanks.

 

[jmeckley]

The problem here is that as you know human error can even cause the retail price to be wrong too

at some point you have to assume the end user can enter data correctly. using the retail price and markup you can calculate a percent increase, if it's out of range ask the user to confirm the mark up.

Ok I was thinking a kind of recursive function to search all my transfers from hq to outlet1 to outlet2 etc. Do you think this will work ? Thanks.

this may work, but...

from a business perspective, who is checking for errors? and how are they checking? this is what should be modeled in code. whether or not it's a recursive function, automated service, or a gui, is irrelevant in determining how it is currently done.


Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newtomysql]

Dear Jason,
I totally agree on the basis that we assume the use doesnt make any mistake but in real life mistakes do happen and we need some form of corrections. About my solution here I am maintain a log to contain the changes made on the markup price. Say one of the log say on 12/1/2009 4.45 pm markup price change from 0.10 to 100. So obiviously in my recursive function I need to first search all those transfer done >= '12/1/2009 4.45pm' from Hq to individual outlets right ? So do you have any idea to add on my recursive function? Thanks.

 

[jmeckley]

I think we are talking on different levels. you looking at the technical code for the specific purpose of finding errors. I'm looking at the system as a whole saying where does this functionality fit?

if the business model is to:
1. review the transfer slips
2. look for price changes over a certain percentage
3. issue a credit (or something) to the outlet for the corrected amount

then in your system I would:
1. create an object to view transfers for a specific product for a period of time.
2. compare each transfer to a set of warning objects.
3. if the warning is triggered, issue a notification to correct the warning.

public class TransferReview
{
   private IEnumerable<Warnings> warnings;

   public TransferReview(IEnumerable<Warnings> warnings)
   {
       this.warnings = warnings;
   }

   public void review(Product product)
   {
       foreach(var transfer in product.transfers)
       {
           foreach(var warning in warnings)
           {
                if(!warning.should_be_triggered(transfer)) continue;
                warning.send_notification_about(transfer);
           }
       }
   }
}

one warning could be percentage of markup is too much. another might be the transfer between locations is not allowed. there could be more.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newtomysql]

Dear Jason,
So you idea about creating an object will be how and when will it run is it a service or program ? The problem here with me is to track all the transfer which was originated from Hq and there after a chain of inter outlet transfer and thisis where I am stuck. So that is why I ask was thinking of using recursive to handle this? Thanks.

 

[jmeckley]

what objects (code) are involved with making a transfer?
[tt]product, location, ...[/tt]

Or, are you using a data driven approach, where most of the work is CRUD operations?
[tt]update location_product set quantity = x and markup = y where ...[/tt]

both are solvable. I find if your using a domain driven approach, the problem could be easier to solve. If you're using a data driven approach it's not impossible.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newtomysql]

Dear Jason,
I am sorry I just would like to know what is the different between domain approach and code approach ? Can I say domain meaning it the design and analysis is it ? Thanks and sorry cause I am a bit new here.

 

[jmeckley]

when I say domain, I'm thinking in terms of Domain Driven Desgin (DDD) where the central focus of the system is the behavior of the domain. everything else, GUI, logging, security, database, hardware, etc... it's all there to support the domain.

By data driven I mean, the database is central to the application. everything is processed in terms of CRUD operations (create, read, update delete). the domain is anemic, if there is one. most operations go directly from the GUI to the database.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newtomysql]

Dear Jason,
Guess from domain is better right.So in your suggestion what must I develop now a program to rectify on this is it ? Thanks.

 

[jmeckley]

out of school I thought i could program and everything I wrote had the gui talking to a stored proc. it's not wrong, but it's very difficult to change. for smaller systems this is not a problem.

2 years ago I, literally, stumbled upon the ideas of what OOP really means, that lead to unit testing, that lead to test driven development which has close ties with domain driven design. all this radically changed how I approach development and how I structure my code.

thinking of your problem I approach this as how would the domain behave to 1. track transfers and 2. how could to adjust an existing transfer. I'm not concerned too much with how this information will be persisted to the database. that's a different concern entirely. Important, but different.

I'm not suggesting you rewrite the current system, or create an external system to manage transfer corrections. The sense I am getting from our conversation is
1. there does not seem to be a domain model in this system, just GUI and database with plumbing code in between.
2. the system models part of the business process, but skips intermediate steps that were/are not required at this point in time.
3. adding new functionality is a massive undertaking which requires re-coding large portions of the application.

all 3 are very common in our profession, unfortunately.

back to the current problem at hand.
1. does your system currently track transfer history?
no : start here, you need to track the history, before you can change it.
yes : then, can you trace transfer X all the way back to the original transfer from HQ?
no : you'll need this trail to manage adjustments
yes : you should have what you need to correct the problem.

Jason Meckley
Programmer
Specialty Bakers, Inc.