sqlite数据库架构 - 引用多行 [英] sqlite database architecture - referencing multiple rows

问题描述

我有一个表材料，其中包含（我正在使用web2py）：

I have a table materials which contains (I'm using web2py):

materials_db.define_table('materials',
    Field('name','string'),
    Field('reference','string'),
    Field('manufacturer','string'),
    Field('date_added','date'),
    Field('density','double'), # Density - kg/m³
    Field('emissivity','double'),
    Field('specific_heat','double'), # Specific Heat - kJ/kg.K
    Field('thermal_conductivity','double') # Thermal conductivity - W/m.K
    )

我现在要创建一个表构造。每个结构都是以有序的方式组装任何数量的材料，例如像这样：

I now would like to create a table constructions. Each construction is an assembly of any number of materials in an ordered way, e.g. something like this:

+------------+-------+
|  Material  | Layer |
+------------+-------+
| Concrete   |     1 |
| Airgap     |     2 |
| Insulation |     3 |
| Plaster    |     4 |
+------------+-------+

层应该能够改变施工中材料的位置。建筑将有新的属性，将从使用的材料的属性和施工中的位置计算。

Layer should enable changing the location of a material in the construction. Constructions will have new properties that will be calculated from the properties of the materials used and the location within the construction.

我真的不知道如何去关于它。构造中的层的值必须是唯一的，但是在构造之间显然不能是唯一的，即每个构造都可以具有值为1的层。

I don't really have a clue on how to go about it. The value for layer within a construction must be unique, however obviously must not be unique between constructions, i.e. every construction can have a layer with the value 1.

我需要为每个构造创建一个新的表，然后在我的表结构中引用所有这些表。这是我目前唯一含糊的想法，但是看起来不太对...有没有一个很好的方法呢？

Do I need to create a new table for each construction and then reference all these table in my table constructions? That's the only vague idea I have at the moment, however that doesn't seem quite right... Is there a good way of doing this?

推荐答案

我不熟悉web2py，但纯粹从数据库的角度来看，您的模型应该如下所示：

I'm not familiar with web2py, but purely from the database perspective, your model should probably look like this:

• 每个建筑都有一个


• 您的BOM ¹ 中的每一行都是一个通过复合键{CONSTRUCTION_ID，LAYER}识别。

• Each construction has one row in CONSTRUCTION table.
• Each material one row in MATERIAL table.
• Each line in your BOM¹ is one row in CONSTRUCTION_MATERIAL table and is identified through the composite key {CONSTRUCTION_ID, LAYER}.

换句话说，这是构造和材质之间的多对多关系，CONSTRUCTION_MATERIAL表用作结或链接表 ² 。如果需要，您可以在交汇表中添加数量等字段。

In other words, this is a many-to-many relationship between constructions and materials, and the CONSTRUCTION_MATERIAL table acts as a "junction" or "link" table². You can add fields such as "quantity" to the junction table, if necessary.

¹ 条例草案或资料

² 虽然略有异常：MATERIAL_ID不属于允许相同材料在相同结构的不同层中的关键。相反，如果相同的材料只能在每个结构中出现一次，只需复制另一个键：{CONSTRUCTION_ID，MATERIAL_ID}。

² Albeit slightly "unusual": the MATERIAL_ID is not part of a key to allow the same material in different layers of the same construction. In contrast, if same material can appear only once per construction, just make another composite key: {CONSTRUCTION_ID, MATERIAL_ID}.

这篇关于sqlite数据库架构 - 引用多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！