If I’ve understood this correctly you should be able to get your result as follows:
Table 1: Materials
Have the primary field be the unique serial code, add any other fields you need.
Table 2: Projects
Have the primary field be the project name (or some other identifier).
Table 3: Project items
Have the primary field be the product code. Then you can have a secondary field which is a linked field which only accepts one record and links back to the ‘Materials’ table. Then have another linked field that accepts one record and links to the ‘Projects’ table.
You can then choose to also sync across to the ‘Project items’ table (via Lookup fields) any details you want to be universally shared across all project materials linked to the same master ‘material’ record. All the other fields can be customised for that Project specific version of the material.
Hopefully that makes sense and answers your question!