zszucs Posted April 26, 2008 Share Posted April 26, 2008 I'm putting together an online ordering sysytem. The problem I'm having is that the client wants to edit products but not change prices in the order history as a result of the edit so the prices there would be accurate for the time. I think this can be overcome by adding the product_price in with the order_detail. The problem is when they want to delete products. If they delete them, the order history would either get deleted with the product getting deleted if it was ordered or leave it as orphaned in the order history. I suggested a show or not show option for the product but they weren't too happy with that. Any suggestions? Scaled down version of how it works now: orders: id user_id order_detail id order_id product_id product_price Quote Link to comment https://forums.phpfreaks.com/topic/103044-db-design-when-ordering-items/ Share on other sites More sharing options...
zszucs Posted April 28, 2008 Author Share Posted April 28, 2008 I was thinking that maybe I can remove teh product id from the order _detail. Apparently, they don't want to do things like query what's been selling. Are there any pitfalls for taking this approach? Quote Link to comment https://forums.phpfreaks.com/topic/103044-db-design-when-ordering-items/#findComment-528641 Share on other sites More sharing options...
zszucs Posted May 1, 2008 Author Share Posted May 1, 2008 anyone? Quote Link to comment https://forums.phpfreaks.com/topic/103044-db-design-when-ordering-items/#findComment-530970 Share on other sites More sharing options...
Fearpig Posted May 1, 2008 Share Posted May 1, 2008 A simple fix would be to populate a different table with order history details, the histroy table would contain something like - History Line ID (autogen integer to define seperate lines as 1 order may have many products) Customer ID Order Number Product ID Purchase Date Item Description Price That way the contents of your product table would be irrelevant to the customer history. Is that any help? Quote Link to comment https://forums.phpfreaks.com/topic/103044-db-design-when-ordering-items/#findComment-531060 Share on other sites More sharing options...
zszucs Posted May 1, 2008 Author Share Posted May 1, 2008 if you delete a product, wouldn't that leave the history table with orphans? Quote Link to comment https://forums.phpfreaks.com/topic/103044-db-design-when-ordering-items/#findComment-531263 Share on other sites More sharing options...
Barand Posted May 2, 2008 Share Posted May 2, 2008 Don't physically delete products but flag them as "deleted" instead. Quote Link to comment https://forums.phpfreaks.com/topic/103044-db-design-when-ordering-items/#findComment-531578 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.