库存系统数据库设计
我正在尝试为一家公司制作一个库存系统，当用户将物品输入库存时没有问题，如下表所示，但是我如何知道当某些物品售出时还剩下多少物品？ 例如我购买了 100 个包，Y 先生购买了 20 个包，系统将如何显示剩余 80 个包？ 任何帮助将不胜感激。 谢谢
CREATE TABLE `inventory` (
`inv_id` int(11) NOT NULL AUTO_INCREMENT,
`inv_reference_no` varchar(40) NOT NULL,
`inv_part_no` varchar(100) NOT NULL,
`inv_category_id` int(11) NOT NULL,
`inv_product_name` varchar(200) NOT NULL,
`inv_quantity` int(11) NOT NULL,
`inv_description` varchar(500) NOT NULL,
`inv_cost_price` float(12,2) NOT NULL,
`inv_cost_sub_total` float(14,2) NOT NULL,
`inv_product_type` enum('cons','serv','stock') NOT NULL,
`inv_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`inv_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
I am trying to make an inventory system for a company, when user will enter item to an inventory there is no problem as the table is shown below, however how do i know how many item left when some of the item is sold ? for example I purchased 100 bags and Mr Y purchased 20 bags, how will system show 80 bags left ? Any help would be appreceated. Thanks
CREATE TABLE `inventory` (
`inv_id` int(11) NOT NULL AUTO_INCREMENT,
`inv_reference_no` varchar(40) NOT NULL,
`inv_part_no` varchar(100) NOT NULL,
`inv_category_id` int(11) NOT NULL,
`inv_product_name` varchar(200) NOT NULL,
`inv_quantity` int(11) NOT NULL,
`inv_description` varchar(500) NOT NULL,
`inv_cost_price` float(12,2) NOT NULL,
`inv_cost_sub_total` float(14,2) NOT NULL,
`inv_product_type` enum('cons','serv','stock') NOT NULL,
`inv_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`inv_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。
评论（2）
你可以在你的表中添加字段说“in_stock”，它将存储剩余的产品数量。
或者您可以在每次购买任何商品时减少您的数量字段。
希望能帮助到你
You can add field say "in_stock" in you table which will store the quantity of products left.
Or you can decrement your quantity field everytime any item is purchased.
Hope it helps
好吧，它应该看起来很简单，因为您已经有一个可以更新的“inv_quantity”字段。 我不确定您是否在弄清楚如何更新它时遇到问题。
这是一些可以帮助您的部分伪代码。 它可以从
UPDATE inventory SET inv_quantity = inv_quantity - quantity_purchased WHERE inv_id = purchased_product_id开始。不久之后，您将
SELECT inv_quantity FROM inventory WHERE inv_id = purchased_product_id。 变量 quantity_purchased 和 purchased_product_id 必须由您提供，但是您要随应用程序发送表单数据。Well, it should seem straightforward as you already have an 'inv_quantity' field that you could update. I'm not sure if you're having trouble with figuring out how to update it.
Here's some partly pseud-code that could help you. It could start out as
UPDATE inventory SET inv_quantity = inv_quantity - quantity_purchased WHERE inv_id = purchased_product_id.Then soon after, you would
SELECT inv_quantity FROM inventory WHERE inv_id = purchased_product_id. The variables quantity_purchased and purchased_product_id will have to be supplied from you with however you're sending form data with your application.