Hello all,
Ok, this is very "generic" question. We currently have a SQL Server database for which we need to develop an application in ASP.NET with will contain all the business logic in C# Web Services.
The thing is that, architecturally speaking, I'm not sure how to design the web service and the data management. There are many things to consider :
We need have very rapid access to data. Right now, we have over a million "sales" and "purchases" record from which we need to often calculate and load the current stock for a given day according to a serie of parameter. I'm not sure how we should preload the data and keep the data in the Web Service. Doing a stock calculation within a SQL query will be very lengthy. They currently have a stock calculation application that preloads all sales and purchases for the day and afterwards calculate the stock on the code-side.
We want to develop powerful reporting tools. We want to implement a "pivot table" but not sure how to implement it and have good performances.
For the reasons above, I'm not sure how to design the data model.
Anybody can give me any guidelines on how to start, or from their personnal experiences (what have you done in the past ?)
I'm not sure if it's possible to make a bounty even though the question is new (I'd put 300 rep on it, since I really need something). If you know how, let me know.
Thanks