以不同的购买价格和库存购买产品的最佳方法是什么?截止日期? [英] What is the best way to stock products with different purchase prices & expiration date?
问题描述
我正在使用PHP，Mysql开发库存系统.该系统具有购买模块，以增加/更新/增加产品的数量和失效日期以及产品的到期日.销售模块以减少/淘汰产品数量.
I'm developing a stock inventory system using PHP, Mysql. This system have purchase module to add/update/increase quantity and expire date of products & sale module to decrease/out the quantity of products.
现在是什么让我感到困惑，而我真正的问题是
Now what is creating confusion to me and what is my real question is that
- 假设我们的库存中有产品A1，数量为20，价格为购买产品A1的价格为15美元.
- 现在我们要购买更多该产品，例如，我们想购买100多件PRODUCT A1产品，但现在PRODUCT A1的新价格为18美元.
将这些物品分开以了解在销售页面上以15美元的价格和18美元的新价格购买了多少产品的最佳方法/逻辑是什么.
What is best way/logic to make these items separate to know how much products are purchased with price $15 and new price $18 on sale page.
-
我是否必须在数据库中存储2个或更多数量列?
Should I have to store 2 or more quantity column in database?
我是否应该在数据库中再存储一次相同的产品?
Should I have to store same product more then once in my database?
目前，我生成的平均值分别为15美元和18美元，然后加上此值作为产品的购买价格.
Currently I'm generating average of $15 and $18 then adding this value as purchase price of product.
推荐答案
哦，亲爱的，您的问题表明您真的在数据库设计的开始.您是否应该在这么早的阶段解决这种复杂性的问题?好吧，为什么不呢?
Oh dear, your questions indicate that you're really at the beginning of database design. Should you tackle a problem of this complexity at such an early stage? Well, yes, why not...
您的问题的答案是:1.不！2.也许.
The answers to your questions are: 1. NO!!! 2. Perhaps.
第一个想法只是愚蠢的.想一想.如果您获得产品的三种不同价格，那么您将需要三列，依此类推……这不是一个好主意.
The first idea is just stupid. Think about it. What if you get three different prices for a product, then you would need three columns, and so on... not a good idea.
第二个选项似乎是一个很好的解决方案.您有一排20件商品的价格为15美元，另一排100件商品的价格为18美元.您可以使用此功能执行大多数操作.平均价格?好:(20 * 15 + 100 * 18)/(20 + 100).简单的.但是它很快变得更加复杂.如果您向客户出售了110件商品怎么办?您将需要更改两行，但是如何?第一行20条，第二行90条?或全部100个来自第二个和10个来自第一个.您不能忽略此问题，必须对如何执行设置严格的规则.您会发现，复杂性将继续增加您添加到系统中的更多选项.可以，但是不会屈服.
The second option seems like a good solution. You have one row of 20 items at $15 and another row of 100 items at $18. You could do most things with this. The average price? Well: (20*15 + 100*18)/(20 + 100). Easy. But it quickly gets more complex. What if you sold 110 items to a customer? You will need to change two rows, but how? 20 items from the first row and 90 from the second? Or all 100 from the second and 10 from the first. You cannot ignore this problem, you have to set strict rules on how to do this. And you will find that the complexity just will keep on increasing the more options you add to your system. It will work, but it will be unyielding.
但是，您还没有提到第三个选择:将库存中的每个项目在表中分配给自己的行.这似乎浪费了数据库空间，但是请耐心等待，它确实可以简化事情.平均价格?很简单，只需计算包含该价格的列的平均价格即可.即使每件商品都有不同的价格，也会同样复杂.从库存中提取物品的规则也很简单:只需使用FIFO或FILO系统.
There is however a third option you haven't mentioned: Give every item in stock its own row in your table. This seems like a waste of database space, but hang on, it does simplify things. The average price? Simple, just compute the average price of the column containing the price. Even if every item has another price, it will be equally complex. Rules to fetch items from stock can be straightforward too: just use a FIFO or FILO system.
因此，最后一种解决方案显然是解决之道.
So this last solution is clearly the way to go.
这篇关于以不同的购买价格和库存购买产品的最佳方法是什么?截止日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
