sqlite database architecture - referencing multiple rows

Question

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.

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?

Answer 1

You have two nouns: material and construction. Any material item may go into multiple constructions and any construction may consist of multiple materials. This is a classic many-to-many relationship. The nouns become entities which are kept in tables. A cross table defines the relationship:

create table MatConst(
  MatID int not null, -- Foreign key to Material table
  ConstID int not null, -- Foreign key to Construction table
  Qty number not null,  -- The number, volume or weight of Mat in Const.
  primary key( MatID, ConstID )
);

The two foreign keys become the primary key for the relationship table so for each thing being constructed, there can be only one entry for concrete, one for plaster and so on. The Qty field allows for amounts like 57 (pounds of concrete) or 150 (square feet of plaster). You may, of course, add other columns that describe the relationship as needed.

Answer 2

Write down statements you need to describe application situations.

Each statement gives a table. A table contains the rows that make its statement true.

materials(material_id,name,...) // material [material_id] has name [name] and ...
constructions(construction_id,purpose,...) // construction [construction_id] is good for [purpose] and ...
construction_layers(construction_id,material_id,layer) // construction [construction_id] layer [layer] is [material_id]

Constrain the database to states that can arise.

Only some application situations can arise. The statements plus those situations mean only some database states can arise. Tell the DMBS so it can stop other states. Say it in constraints.

Keys (PKs and UNIQUE column sets) say a base table's value is constrained. FKs express that tow tables are mutually constrained.

materials(material_id,name,...) // material [material_id] has name [name] and ...
    pk (material_id)
constructions(construction_id,purpose,...) // construction [construction_id] is good for [purpose] and ...
    pk (construction_id)
construction_layers(construction_id,material_id,layer) // construction [construction] layer [layer] is [material_id]
    pk (construction_id,layer)
    fk (construction_id) references constructions (construction_id)
    fk (material_id) references materials (material_id)

It would be nice if a DBMS would for each construction_layers row

CHECK(layer >= 1)

but that might or might not be possible without triggers with a particular DBMS.

We would also want to check that each construction's layers are contiguous (maybe not all the time). That you would have to do by triggers under current DBMSs.

Limit what you say in a statement in certain ways.

The only properties you should have in a base table other than its key columns are those that are a function of an entire key. (This gives a table in 5NF.) (A computed column is ok any time.)

Other information not derived from given base tables should be in a separate base table.

Any other table you want should be got as a query or view.

Answer 3

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

• 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}.

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.

---

¹ Bill or materials.

² 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}.