DeX Posted June 29, 2015 Share Posted June 29, 2015 I know of all the ways to dump it to a text file but I want to save a database table into another match table. I have web software which allows users to enter some inputs and save a create a quote for their client. At the time of the quote, all of the building products in the database have an associated price and this pricing can change at any time. When they save the quote, I want to save all 300 products and prices into another table so I can go back later and see what the prices were when that quote was done. I don't want to load a quote from a month ago and have it price based on current pricing, I want it to price using the prices from a month ago. Right now I'm simply selecting all 300 rows and dumping them into a match table with the quote ID. That's causing it to take about 30-50 seconds to save the quote, along with a couple of other tables I'm saving as well. I'd like to make the quote saving much faster, is there a faster way to get this snapshot of the database table when the quote is saved? Quote Link to comment Share on other sites More sharing options...
fastsol Posted June 29, 2015 Share Posted June 29, 2015 How about a quote items reference table. Just store the quote_id, product_id and quoted_price. All of which you should have the data for when submitting the quote. Then you don't have duplicated table data with the products and you just display the quoted price later if you need to view it. Quote Link to comment Share on other sites More sharing options...
DeX Posted June 29, 2015 Author Share Posted June 29, 2015 How about a quote items reference table. Just store the quote_id, product_id and quoted_price. All of which you should have the data for when submitting the quote. Then you don't have duplicated table data with the products and you just display the quoted price later if you need to view it. Yes, that's what I'm doing, it's just 4 columns: - id - quote_id - product_id - price Then I also have to save the sales person's commission rates: - operations commission - sales commission - sales management commission - 4 or 5 other commissions Then I save the rates: - winter build rate (used if building in winter) - remote labour rate (used if building far enough away from head office) And then I save minimums - minimum labour (used if labour is less than the minimum) - minimum materials (used if materials are less than the specified minimum) - minimum truss price (used if total truss price isn't at least the specified minimum) These are all things required for looking back at older quotes and they all come from separate tables and get stored into separate match tables. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted June 30, 2015 Share Posted June 30, 2015 (edited) didn't see this thread before. the 'correct' way of doing this is to store the pricing information in a table using the productId, the start date (or datetime) and the end (or a NULL) date (or datetime.) each time the price changes for an item, you would set the end date (or datetime) for the previous price and insert a new row with the start date (or datetime) and the new price. any query to calculate the price would use the date (or datetime) of the quote to match the correct pricing information. any chance that your thread about the switch/case statement taking a long time is doing a ton of database queries (in a loop) that could account for the bad performance? Edited June 30, 2015 by mac_gyver Quote Link to comment Share on other sites More sharing options...
Barand Posted June 30, 2015 Share Posted June 30, 2015 the 'correct' way of doing this is to store the pricing information in a table using the productId, the start date (or datetime) and the end (or a NULL) date (or datetime.) Just my 0.02 worth, but if you store a date of 9999-12-31 instead of NULL in the end date of the current price then it makes joins easier when you want the price that was applicable at the time of the transaction EG SELECT .... FROM transaction INNER JOIN price ON transaction.date BETWEEN price.startdate AND price.enddate 1 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted June 30, 2015 Share Posted June 30, 2015 i was going with - ... BETWEEN startdate AND IFNULL(enddate,'9999-12-31') Quote Link to comment Share on other sites More sharing options...
Barand Posted June 30, 2015 Share Posted June 30, 2015 Will that use the indexes? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted June 30, 2015 Share Posted June 30, 2015 no. Quote Link to comment Share on other sites More sharing options...
gizmola Posted June 30, 2015 Share Posted June 30, 2015 I agree with both mac and Barand, but more so in practice I've used Barand's method. I like to call that 'max end date' the 'PERPETUITY_DATE'. Makes for a nice constant. Other benefit of that, is that you have a complete price history for any item, and there's often some nice analysis you can do with that data and visualizations like charting the price variation of items over time. Quote Link to comment Share on other sites More sharing options...
DeX Posted July 1, 2015 Author Share Posted July 1, 2015 (edited) didn't see this thread before. the 'correct' way of doing this is to store the pricing information in a table using the productId, the start date (or datetime) and the end (or a NULL) date (or datetime.) each time the price changes for an item, you would set the end date (or datetime) for the previous price and insert a new row with the start date (or datetime) and the new price. any query to calculate the price would use the date (or datetime) of the quote to match the correct pricing information. any chance that your thread about the switch/case statement taking a long time is doing a ton of database queries (in a loop) that could account for the bad performance? Wow, that is genius! I actually laughed when I read it because of how smart that is. Because the system gets the overall price for the quote, it goes to the database 300 times to get the prices for all the different products with 300 individual database calls to the same table. I think it might only make the calls for products with a positive quantity though, now that I think about it. EDIT - These calls are only done when the quote is saved, not on edits. I'm not concerned right now about speed of saving quotes, currently I'm focused on edits. Edited July 1, 2015 by DeX Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted July 1, 2015 Share Posted July 1, 2015 if your bom/cart is memory/session based, to display it with pricing, you would extract all the productId's (since these are array keys now, you can use array_keys() to do this all at once.) you would then implode the array of productId's to make a comma separated list that will get used in ONE database query, using an IN(...) term in the WHERE clause, to retrieve all the prices for those productId's, and store the result in an array, using the productId as the array key. as you loop over the contents of the bom/cart to display it, you would use the productId to get the price from the array you just made from the single query. if your bom/cart is database table based, you would just join the bom/cart with the pricing table, then just retrieve and display the results. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.