mysql - 关于我的业务逻辑的数据库设计

Question

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

a) Place a new order for customer. (an order is a group of three related components, estimate, invoice and purchaseorder)

b) After placing an order a new estimate can be generated. an order will have only one estimate.(An estimate consist of item details)

c) With reference to an estimate of the order. an invoice can be generated. an invoice qualify for the discount of price. apart from item details, an invoice consist of some expenses.an order can contain only one invoice

d) with reference to an invoice of the order, PurchaseOrder can be generated. PurchaseOrder consist of item information about vendor purchase. an order can contain multiple PurchaseOrder.

这是我想出的数据库表设计。

虽然一切看起来都不错，但我很难决定在哪里存储属于订单的特定估算、发票或采购订单的项目列表。

我想到了几种解决方案。

Approach A : create different tables for item list for each. (estimate, invoice and purchase order)
table : estimate_item , invoice_item , purchaseorder_item.(this tables contains columns similar to that of order_item in above image).
problem: problem with this approach is all the three tables consist of identical columns storing identical information, the only difference is foreign key that will be stored.

Approach B: create one item list table order_item
tablename: order_item
problem: not sure what to store as foreign key in this table since the foreign key can be from three different table. i thought of few way of handling foreign keys in this table as follows.

1)foreignKey table reference column: type (example values: estimate, invoice, purchaseorder) foreignKey column: type_id(consist of foreignKey of any of three tables)
problem: i am using naming convention for column names for example column name ending with tablename_id defines the foreign key. and this method violate the rules.

2) foreignKeyColumn: order_id , estimate_id , invoice_id , purchaseorder_id.
problem: unnecessary foreign key columns defined.

我想知道我应该如何将外键存储在 order_item 表中，以便它识别订单以及它所属的估算/发票/采购订单。

表的关系是:

id is the primary key for all the tables

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 都有自己的项目并且彼此没有关系。

Answer 1

嗨，我不确定这种关系是如何发生的。例如，您将“估计”指向“订单项目”，但我看不到您必须使用什么键才能进行连接(或查找)。作为另一个“命令”指向“估计”但是这两个是如何加入的？我没有看到这两个实体具有的任何共享属性。

我假设“id”只是使每个特定表中的行唯一的东西，而不是对应用程序有值(value)的 id。所以，我认为您需要将 estimate.reference number 携带到“order item”表中。这只是一个粗略的评论。

另外，如果键列在最前面，为了清楚起见会有所帮助。所以在'order item'中，你有属性'order id'(看起来是FK)埋在其他属性列表的末尾。使这很难阅读。