基于我的业务逻辑的数据库设计

Question

我正在构建一个发票应用程序，包含以下业务逻辑。

a)为客户下新订单。（一个订单由三个相关组件组成，即估算、发票和采购单）

b)下订单后可以生成新的估价。一个订单只有一个估价。（一个估价包括项目详细信息）

c)参照订单的估价可以生成发票。发票有资格享受价格折扣。除了项目详细信息外，发票还包括一些费用。一个订单只能包含一个发票。

d)参照订单的发票，可以生成采购单。采购单包含有关供应商采购的项目信息。一个订单可以包含多个采购订单。

这是我设计的数据库表格设计。

虽然看起来都不错，但我在决定将属于特定估价、发票或采购订单的物品清单存储在哪里时遇到了困难。

我想出了几个解决方案。

方法 A : 为每个列表（估计、发票和采购订单）创建不同的表格。

表：estimate_item、invoice_item、purchaseorder_item。（这些表包含与上面图像中order_item相似的列）。问题：这种方法的问题在于所有三个表格都包含相同的列，存储相同的信息，唯一的区别是将存储外键。

方法 B: 创建一个项目列表表order_item。表名：order_item问题：不确定在此表中存储外键，因为外键可以来自三个不同的表。我考虑了几种处理此表中外键的方法，如下所示。

1) foreignKey表引用列：类型（示例值：估价、发票、采购单）foreignKey列：type_id（包含任何三个表的外键）

问题：我正在使用列名称的命名约定，例如以tablename_id结尾的列名定义了外键。这种方法违反了规则。

2) foreignKeyColumn：order_id、estimate_id、invoice_id、purchaseorder_id。

问题：定义了不必要的外键列。

我希望知道如何在 order_item 表中存储外键，以便它可以标识它所属的订单和估计/发票/采购订单。

表之间的关系如下：

id 是所有表的主键。

table name: order relates to (contact, estimate, invoice, shipment) tables.
column name: contact_id (foreign key(referring to id column of the contact table)).
column name: estimate_id (foreign key(referring to id column of the estimate table)).
column name: invoice_id (foreign key(referring to id column of the invoice table)).
column name: shipment_id (foreign key(referring to id column of the shipment table)).

tablename: purchaseorder (this have one to many relationship with order table)
column name: order_id (foreign key(referring to id column of the order table)).
column name: contact_id (foreign key(referring to id column of the contact table)).

这个问题涉及如何在order_item表中存储外键。

谢谢。

更新1：

请注意，每个表estimate、invoice和purchaseorder都将有自己的项，并且彼此之间没有关系。

- Ibrahim Azhar Armar

---

@eggyal 对不起，是我弄错了，我上传了错误的图片。那只是我正在进行的思考过程。我已经更新了所有字段的列，并更新了我的图片。 - Ibrahim Azhar Armar

但我的观点仍然存在...您有多个order_item引用每个order，而每个order又引用相应的invoice、estimate和/或purchaseorder，除了ERD中的箭头之外，有什么问题吗？ - eggyal

是的，每个订单都包含多个项目，因此有多个order_item，我必须将order_item与invoice、estimate、purchaseorder相关联，这些又属于一个订单。 - Ibrahim Azhar Armar

我感觉我们在打转。我的第一个问题是“为什么需要从每个order_item引用后面的表格，考虑到order_item引用了一个order，而order又引用了那些表格”？你是在暗示同一订单中的项目可能与不同的invoice、estimate或purchaseorder相关吗？我认为你试图解决一个不存在的问题。 - eggyal

同意，图表不够清晰，因为我不太擅长设计数据库，正在学习中。上述命名约定取自Doctrine ORM，Zend Framework也推荐使用相同的命名约定，而我同时在使用这两个工具。 - Ibrahim Azhar Armar

Answer 1

你好，我不确定关系是如何建立的。例如，你有一个指向“订单项”的“估算”，但我没有看到你有什么键来进行连接（或查找）。另外，“订单”指向“估算”，但这两个实体是如何连接的？我没有看到这两个实体共享的任何属性。

我假设“id”只是为了使每个特定表中的行唯一，但它们不是对应用程序有价值的ID。因此，我认为你需要将估算参考编号带入“订单项”表中。这只是一个粗略的评论。

另外，如果首先列出键，会更清晰明了。所以在“订单项”中，你有一个属性“订单ID”（似乎是一个FK），埋在其他属性列表的末尾。这使得阅读变得困难。

- Don Wool

---

我更新了图片，因为之前上传了错误的图片。我一直在使用明映射工具。我会更新问题，说明每个表格之间的关系。 - Ibrahim Azhar Armar

哦，好的，明白了。谢谢，这会帮助我更仔细地看这个。 - Don Wool

Answer 2

如果我理解得正确，与订单相关联的每个文档（例如 估价，采购订单 和/或 发票）可能包含不同的物品清单。

如果是这种情况，我可能会按照以下方式创建一个 文档 表：

CREATE TABLE Documents (
  DocumentId   INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  OrderId      INT NOT NULL,
  -- you can move any fields common to all document types here
  -- e.g. date created, reference #, etc.
  FOREIGN KEY (OrderId) REFERENCES order (id)
);

然后你的order_item、estimate、purchaseorder和invoice表都引用了它们在这个表中对应的条目：

ALTER TABLE [tablename]
  ADD COLUMN DocumentId INT NOT NULL,
  ADD FOREIGN KEY (DocumentId) REFERENCES Documents (DocumentId)
);

这是你想要的吗？

- eggyal

---

如果表格之间的键名相同，那就太好了。例如，他说：estimate_id（外键，指向estimate表的id列）。因此，如果在estimate表中将id命名为“estimate_id”，而不是“id”（如果是这样的话），那么会更清晰明了。有了许多表格，很难知道“id”代表什么。 - Don Wool