如何为此订单情况设置数据库表 [英] How should I set up database tables for this order situation

问题描述

我正在为一家印刷公司建立一个网络应用程序，并尝试为我的数据库确定最佳设计。

I'm building a web application for a printing company and am trying to determine the best design for my database.

我有一个订单表。每个订单有很多证明。但是，有两种不同的证明：电子证明和实物证明。此外，如果它的电子证明，我需要存储图像源，如果它的物理证明，我需要存储一个跟踪号码。

I have an orders table. Each order has many proofs. However, there are two different kinds of proofs: electronic proofs and physical proofs. Furthermore, if its an electronic proof, I need to store the image source, and if its a physical proof, i need to store a tracking number.

每个证据都有许多相关的评论。

Every proof has many comments associated with it.

如果我只能有一个证明表和一个 表，但根据证明类型，跟踪图片来源和跟踪号码的最佳方式是什么？

It would be nice if i could just have one proofs table, and one comments table, but what is the best way to keep track of the image source and tracking number based on the proof type?

为 electronic_proofs 和 physical_proofs 创建单独的表，但这将需要单独创建 electronic_proof_comments 表和 physical_proof_comments 表。

I've considered creating separate tables for electronic_proofs and physical_proofs, but that would require making a separate electronic_proof_comments table and physical_proof_comments table.

另一个选项是有一个 proofs 表，在这种情况下，我可以有一个 comments 表。但是，如我所见，这将需要三个支持表， proof_types ， image_sources 和 tracking_numbers 。

The other option would be to have a single proofs table, in which case I could then have a single comments table. However, as I see it, that would require three support tables, proof_types, image_sources, and tracking_numbers.

对最好的方法有什么想法，还是更好的解决方法？

Any thoughts on the best way, or an even better way of solving this problem?

推荐答案

正如在另一个答案中提到的，您只需要一个表，并根据类型将其中一个字段留空。然而，我的回答的不同是将订单详情与证明相关联，而不是与订单详情（或示例中的订单商品）的证明相关。

As mentioned in another answer, you only need one table and would simply leave one of the fields blank, depending on the type. However, the difference in my answer would be to relate the order details to the proof rather than the proof to the order details (or order item in their example). Doing it this way allows you to have multiple proofs per order detail.

这是我如何设置它，如果我是你：

This is how I would set it up if I were you:

ORDERS

• OrderID（PK）


• CustomerID（FK）


• Etc ...

ORDERDETAILS

ORDERDETAILS

• OrderDetailsID（PK）


• OrderID（FK）


• ProductID？ （FK）


• 其他...

• OrderDetailsID (PK)
• OrderID (FK)
• ProductID? (FK)
• Etc...

PROOFS

• ProofID（PK）


• OrderDetailsID（FK）


• ProofType


• ImagePath（只是路径，而不是图片）


• TrackingNumber


• Etc ...

• ProofID (PK)
• OrderDetailsID (FK)
• ProofType
• ImagePath (just path, not image)
• TrackingNumber
• Etc...

注释

• CommentID（PK）


• ProofID（FK）


• 评论


• 其他...

• CommentID (PK)
• ProofID (FK)
• Comment
• Etc...

这可能也是明智的打破ProofType到它自己的表，但将没有工作。如果你这样做，你会创建一个ProofType表，并将Proofs表中的ProofType字段更改为引用新ProofType表中的ProofTypeID字段的外键。

It would probably also be wise to break ProofType into it's own table, but will work without. If you were to do that, you'd create a ProofType table and change the "ProofType" field in the "Proofs" table to a foreign key referencing the "ProofTypeID" field in the new "ProofType" table.

希望这有助于！祝你好运！

Hope this helps! Good Luck!

这篇关于如何为此订单情况设置数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！