|
pro.coder
Starting Member
2 Posts |
 Posted - 2008-01-09 : 14:11:05
|
| Hi,I’m developing a Cylinder Tracking Application. Part of which is inventory management. I have 4 types of inward and 4 types of outward which are stored in different 8 masters and details table and 1 Table to store physical Adjustment (i.e. actual stock counted quarterly). I need to 2 ledger reports from these tables.Product Ledger with fieldsDate,ProductID,Openning,Inward,Outward,Closing,PhysicalAdjustCustomerLdger with fieldsCustomerID, Date,ProductID,Openning,Inward,Outward,Closing,PhysicalAdjustReport data should be up to date. All the data will be replicated to Remote server over internet. Some users will access report from remote server. Any inward and outward entry could be edit at any time. Multiple users will access the report(Including Between days with date ranging fom days to months ). There are 100s of customers and Products. I’m using two different solutions for earlier project.1. I’m storing all the data in Productledger and Customerledger. On server date change event I’m importing previous day's closing to today's opening. On all other tables I have written triggers to update corresponding field and closing. If necessary update closing of all other entry who's date is > updated date. This way I get the up-to-date values. Problem here is each update on inward or outward could update 1000’s of entries. When multiple users are involved this could grow exponentially. This works fine for small no of products or when replication is not involved.2. I created two tables ProductClosing and CustomerClosing to store closing as on last day of the month. I have written triggers on other tables to update closing. When user wants the data I use SP to calculate from previous closing till current date. I don’t have Replicate Productledger and Customerledger just two Closing tables with few entries. Remote server user will calculate on their end. This works for replication. But when 1000s of product and customers involved calculation takes lot of time and I have to calculate for each user. This takes lot of server resource.My application is a windows application with limited users. I need a solution to speed things up and should work with replication. As this is new project I’m open to any database design or suggestion even if I have to start from scratch. Sample Input & Output:User1 -> Between 01-01-07 TO 12-02-07Output01-01-07,ProductID1,10,09,0,19,001-01-07,ProductID2,10,09,0,19,001-01-07,ProductID3,10,09,0,19,0……02-01-07,ProductID1,19,00,02,17,002-01-07,ProductID2,19,00,02,17,002-01-07,ProductID3,19,00,02,17,0……12-02-07,ProductID1,17,00,02,15,012-02-07,ProductID2,17,00,02,15,012-02-07,ProductID3,17,00,02,15,0Similarly for Customer Ledger |
|
Database Design Help