How to map out product components from SKUs and bundles?

[modernmatt]

Ok, I'm trying to build a product catalog and inventory management Airtable. I'm trying to work out the basic structure before I get too deep into adding all of my stuff.

We have components, with a Part #, which are sometimes sold on their own as a SKU.

We have Products, which are made up of multiple components, sometimes more than one quantity of each component in a product. These are sold as a SKU

We have Bundles, which are made up of multiple SKUs.

I want to bring in data from another source which shows SKUs sold by SKU and quantity (some are bundles, some are components, some are products), and I want to be able to drill down to the number of each component sold from that same data.

Any suggestions here?

[Xavier_G_Sabate]

Hi, 

If I understood correctly your explanation, the data that you will bring into your sold SKU and quantities into an Airtable base. From that you would like to know by SKU (that can be component or product) the sold quantities, aside you have bundles that contains groups of SKUs.
I would try this design:

• Create a table which primary key is the SKU, either if it comes from a component or a product. You should have a SKU master table
• You can mark the SKU as component or product for later management
• Create an internal link (hierarchical relationship) in that table, so you can relate any component to the product it belongs. This can be probably done automatically using your SKU master table if it contains the relationship  between components and products
• Create a table for bundles that can be linked to the SKU table (either component or product). Again, use your master data
• For the import data, you should create a stage/target table with a key of your choice, either the SKU or a logical combination of fields (sku + sold date) that makes sense for you
• In this import table you need a column containing the real SKU that can be linked to the SKU table, so you will have every record linked to the right SKU

For reporting you can use:

• Your stage/target table as it is, grouping by date, SKU... whatever field is convenient
• Your SKU table since it is related to the stage/target you can segment the data by SKU (either component or product)
• Your bundle table including different SKUs
• Bear in mind that metrics (sold units, amount...) that you have in the stage/target table can be pushed to the SKU (and bundle) tables in the form of rollups, so you can leverage them on different levels of reporting, but be aware of the limitations that Airtable offers for that
• Same happens when you want to display numbers on interfaces, you need to prepare wisely how to aggregate the information

In order to feed the system with new SKUs and Bundles, you can integrate your app in different ways; using the extensions for CSV import, the Synch API, other integrations or even copy / paste (not recommended at all)

Let me know if this explanations works for your purposes of if you have further questions.

Best regards