创建一个简单的（但不适合我）数据库

Question

我正在尝试建立一个数据库来跟踪小型 craigslist 销售业务中发生的情况。

基本上这些人的操作方式就是这样。

1. 假设有人以每本 10 美元的价格购买 3 本书。

2. 他们将其发布在 CL 上

3. 他们以 20 美元的价格出售。

4. 每售出一本书，购买者可获得 10 美元的返还，外加 1/3 的利润。

问题是他们每次几乎都买同一本书，但他们购买的价格却发生了变化。有时，他们以 12 美元或 15 美元的价格购买这本书。如果市场对他们有利的话，他们会购买其他书籍。

我想要做的是建立和库存清单，通过书籍的名称和购买价格来跟踪书籍。

因此，如果我们有 10 本“绿鸡蛋和火腿”书，每本售价 10 美元，而我们有 5 本“绿鸡蛋和火腿”书，每本售价 15 美元。库存清单应反映两个单独的项目。这样投资者的投资就能得到适当的报酬。

我在以这种方式设计数据库时遇到困难。我有以下表格：

产品

• ID PK
• 名称
• 说明

库存

• ID
• 购买日期
• Product_ID PK
• 购买价格 PK
• 数量

采购

• ID PK
• Product_ID
• Product_Quanity
• Purchase_Price

到目前为止，我的推理表明我应该在产品表中跟踪他们正在销售的商品类型。还要跟踪他们购买商品的时间以及在给定时间购买该商品的数量；因此就有了采购表。然后我想建立一个库存清单，但每条记录都需要根据商品及其成本来唯一。所以我决定使用购买价格和产品 ID 的复合键来做到这一点。

如果字段不是主键，是否可以将采购表中的购买价格和产品 ID 关联到我的库存表？到目前为止，我的方法适合我想要实现的目标吗？对于我想做的事情有更好的方法吗？

数据库还有其他元素我没有提到，但如果有必要的话会提及

感谢所有可以帮助我的人。

Answer 1

产品

• ID PK
• 产品名称
• 描述

购买

• ID PK
• 产品ID FK
• 购买日期
• 数量
• 购买价格

>Sale

• ID PK
• ProductID FK
• SaleDate
• Quantity
• SalePrice

Inventory

SELECT ID, ProductName, 
       (SELECT Sum(Quantity) FROM Purchase WHERE ProductID=Product.ID) -
       (SELECT Sum(Quantity) FROM Sale WHERE ProductID=Product.ID) AS NumInStock
FROM Product

以上查询的作用是从产品中返回 ID 和 ProductName表以及计算该商品的库存数量。第一个子查询 (SELECT Sum(Quantity) FROM Purchase WHERE ProductID=Product.ID) 返回已购买的所有匹配商品的总和。然后，它从该总数中减去已售出的所有匹配商品的总和(SELECT Sum(Quantity) FROM Sale WHERE ProductID=Product.ID)，得出库存商品。

要获取以 100 美元购买的 iPad 数量，您可以使用如下查询：

SELECT Sum(Quantity) AS NumiPadsFor100
FROM Purchase WHERE PurchPrice = 100

要获取特定商品的利润，在查询中执行所有操作会有点麻烦，但在代码中执行会很简单。

Answer 2

那么，在产品入库之前应该先购买，对吗？只需使用 PurchasingID 作为 Inventory 表中的外键 - 这将消除在两个表中使用 buy_price 的需要。
编辑：
它将是这样的：
产品
身份PK
姓名
库存描述

身份PK
购买日期
数量
采购_ID FK

采购
身份PK
产品_ID FK
产品_数量
Purchase_Price

根据您的需求，您可能需要在采购和库存之间添加一个额外的表

Answer 3

1 个产品表和 1 个变动表（销售和采购）。这样你的库存就会非常容易。如果流量很大（假设每年超过 50,000 次移动），您可能需要查看这项有趣的研究 。