What is the general view on using Staging tables? We use them a lot for Order imports from External vendors

Question

We have been having a lot of internal debate regarding staging tables. Some are view staging tables as archaic and will prevent the ability to build re-usable services, etc. It is also being communicated that these will prohibit the business to grow and handle expanded business channels.

I am not necessarily for or against either option, but I do know that having the staged data has been a life saver in many occasions and has made it really easy to re-import orders we have had issues with.

Just wanted to see what others thought about staging data and what other methods are being used to handle scenarios similar to ours (Taking orders from external partners, Amazon, etc and importing them into our ERP system).

Thanks,

S

Answer 1

Some places I've worked I've used staging tables, others I've used other techniques. Each one has its own advantages and disadvantages. That said, don't worry about it. If some data feed comes along that requires some method other than what you are doing, then you'll come up with a new solution. Change is driven by requirements.

(personally, when someone comes to me and says "We have to change to X because what we do now is inefficient and bad and witches will come and eat our children", they have this image in their minds that on tuesday, we will have an opportunity to triple our client base but only if we do this new thing, but if we don't get cracking on it now, then we'll miss the opportunity because none of those potential clients is willing to wait even a minute and they'll all demand the exact same thing and we can build exactly what they want right now even though we have no idea what they want HURRY HURRY HURRY AND DON'T BREAK ANYTHING. Which, of course, isn't how anything works. A single client (or whatever) comes along and says "Hey, we want your services, can you accept our XML?" to which the response is always "Sure thing", and then you get tasked with it and can make intelligent decisions, and plan things out. As opposed to the "chicken with its head cut-off" methodology preferred by people who like technical words but hate knowing anything tehcnical)

Answer 2

There is no reason for a debate - you have a working system. Anyone who thinks their "re-usable services" theory can do it better should put up or shut up.

Let them build a test implementation on your development servers for a common high volume scenario, and compare it to the current system - including criteria for recovery and re-import after a failure.

I hear this all the time where I work as well (usually from managers who just read an article about SOA and XML) and in situations dealing with large amounts of data - bulk imports into staging tables can handle a much higher data volume than any type of web service.