物料清单数据库模型 [英] Bill of materials database model

问题描述

我目前正在开展一个项目，该项目应该帮助我们进行库存控制以及我们的采购来组装我们的最终产品。

我们在我们的数据库建模阶段，其中一个要求是生成BOM（物料清单）。

我已经阅读了此线程，并找到BOM的示例数据模型：

概念数据模型和
物理数据模型

但我不知道我完全明白。

我们的最终产品由几个子程序集组成，因此每个子程序集都是 product_hierarchy 表中的一行，最终产品在该表中也是一排。每个子组件由单独（原子）部分组成，每个部分在表 tpart （每个部分都有制造商字段，最小重订单数量和其他特定字段）

当生成BOM时，还应该包含所有单独的部分，所以我不完全清楚如何建模我们的数据库：

1. 一个单独的部分是 product_hierarchy 中的一行，它永远不会是一个'父'（表tpart不再需要）


2. product_hierarchy 和 tpart 之间的N：M关系：每个单位有几个部分;每个部分可以属于多个单位

我倾向于第二种选择，因为部分基本上是一个完全不同的实体一个价格，几个可能的供应商...），而一个组装的实体没有外部（如在我们公司以外的）属性。

任何输入都是赞赏！谢谢！

解决方案

您链接的模型未能解决BOM通常具有的一些主要属性：

• 零件和子装配可以重复使用。例如，在许多装配体中使用相同类型的螺栓是常见的。


• 需要具有 BOM特定数量。例如，重要的是要知道一个装配需要（例如）50个螺栓，但是其他组件可能只需要30个相同类型的螺栓。

这是一个解决这些问题的简单模型：

PART表是顶部组件或子组件或叶子部分。它使用公知的部件号来标识其行，实际上并不是数字，可以包含非数字字符。

BOM表模型多多个关系的PART本身。与任何其他连接表完全没有区别，除了端点表格实际上是同一个表格外。这样，一个子组件或部件可以在多个父组件中重复使用。

除了这个模型之外，你可以自然地添加drawing position或测量单位（例如，油漆可以是BOM的一部分，但以千克而不是块衡量）。

---

/ p>

例如：

>

• 如何处理变更？你有部分版本控制吗？您是否为BOM本身进行版本？


• 不同的供应商可能会使用不同的零件编号来实质上相同的零件。


• 您可能希望跟踪场所（仓库或工厂）部件的储存或组装/生产。


• 您可能需要区分制造和已购买零件。

<您有生命周期工作流程（批准/发布/过时）？

• 您可能想要存储用户定义的属性。属性通常包括质量，体积和材料等等，但可能还有许多其他事项无法预见。


• 您可能希望将物理CAD模型连接到数据库。


• 您可能希望不允许某些用户对数据库进行某些更改（例如，采购部门不能更改程序集结构，至少不需要监督）。 / li>
  
• Etc等...

这些是为什么真正的PDM系统倾向于要复杂如果您真的需要所有功能，您应该考虑使用商业产品，而不是自行重新实施...

I'm currently working on a project which should help us with our inventory control as well as our purchases to assembly our final product.

We're in the stage of modeling our database and one of the requirements is to generate a BOM (Bill of materials).

I've read this thread and found an example data model for BOM:

conceptual data model and physical data model

but i'm not sure I fully understand.

Our final product consists of a couple of sub-assemblies, so each sub-assembly is a row in the product_hierarchytable, and the final product also a row in that table. Each sub-assembly is made out of seperate (atomic) parts and each part is identified in a table tpart (each part has manufacturer field, minimum reorder quantity and other specific fields).

When generating a BOM all separate parts should also be included, so it's not fully clear to me how to model our database:

1. a seperate part is a row in product_hierarchy which will never be one's 'parent' (the table tpart is no longer needed)
2. an N:M relationship between product_hierarchy and tpart: each unit has several parts; each part can belong to several units

I'm leaning towards the second alternative, since a part is basically a total different entity (has a price, several possible suppliers, ...) whereas an assemblied entity has no external (as in: outside our company) properties.

Any input is appreciated! Thanks!

解决方案

The models you linked fail to address some major properties BOMs normally have:

• Parts and sub-assemblies can be reused. For example, it is common for a same kind of bolt to be used in many assemblies.
• There needs to be a BOM-specific quantity. For example, it's important to know that one assembly needs (say) 50 bolts, but the other assembly might only need 30 of the same kind of bolt.

Here is a simple model that addresses these concerns:

The PART table is either a top-assembly or a sub-assembly or a leaf part. It uses a publicly known "part number" to identify its rows, which is not actually a number at all and can contain non-numeric characters.

The BOM table models many-to-many relationship of PART by itself. It's really no different from any other junction table, except both "endpoint" tables are actually the same table. This way, one sub-assembly or part can be reused in multiple parent assemblies.

On top of this model, you can fairly naturally add things like "drawing position" or "unit of measure" (e.g. a paint can be part of BOM but is measured in "kilograms" instead of "pieces").

---

There are more things you might want to do in reality, but are beyond the scope of a simple StackOverflow post like this.

For example:

• How do you handle change? Do you have part versioning? Do you version the BOM itself?
• Different suppliers might use different part numbers for the essentially same part.
• You might want to keep track of "sites" (warehouses or factories) where parts are stored or assembled/produced. A "same" assembly might even have slightly different BOM for different sites.
• You might want to differentiate between "made" and "purchased" parts.
• Do you have a lifecycle workflow (approve/release/obsolete)?
• You might want to store user-defined attributes. Attributes typically includes things such as mass, volume and material, but there may be many others that cannot be foreseen in advance.
• You might want to connect the physical CAD models to the data in the database.
• You might want to disallow certain users from doing certain changes to the database (e.g. procurement department shouldn't be able to change the assembly structure, at least not without supervision).
• Etc, etc...

These are some of the reasons why real PDM systems tend to be complex. If you actually need all that functionality, you should probably consider using a commercial product instead of trying to re-implement it yourself...

这篇关于物料清单数据库模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！