Meta Data on Linked Records

[Tomgerhardt]

I am building an inventory management base for my business and am running into a design problem:

1. I have a table for parts.

2. I also have a table for assemblies. Each assembly record has a field that links to multiple records from the parts table.

Simple, however, I want to be able to say how many of each part goes into an assembly. Parts go into different assemblies in different quantities, so that data can’t (and shouldn’t) be stored in the part record.

How can I say one assembly uses 2 of a part and a different assembly uses 3 of that part. (And I need a table of all the assemblies.)

Hope that makes sense! I would appreciate any solutions.

[Tomgerhardt]

@Kamille_Parks  looks like this is a problem you solved with your base. Have a simple explanation? 

[Kamille_Parks · Accepted solution]

you can take a look at my Meal Planner base as it has a good example of what is commonly referred to as a "junction table". In your case, you want one table for Parts, one table for Assemblies, and one table where each record links a Part to an Assembly; the third table will include a number field to show how many of each Part goes to an assembly.

In the base example below, look at the "Recipe Ingredients" table to see what what your junction table might look like. (Recipe = Assembly; Ingredient = Part)

https://www.airtable.com/universe/expf1Eqp1BC0kvs86/ultimate-meal-planner

[Tomgerhardt]

Thanks!