Inventory Management for Refurbished Video Games

Hey ​@retrorevivialgameco,

I describe something similar, for an inventory management system on this other post. 

I would suggest having:
1. One unique table for products. Within it you’ll find both the products (e.g. bundles) and components individualized.
2. A Composition table, which would work as a junction table to determine which bundles uses which components in what quantities (more on junction tables on the video below)
3. An automation that for each purchase or sale of a bundle, you would get the corresponding line tems for the actual copmonents for it as well.

In this way, you’d be able to track everything at their corresponding unit level. Does this make sense? Feel free to reach out as needed. I’d be happy to help!

 

Completely different matter, but would love to have you join our Airtable Hackathon! Make sure to sign up!!

Mike, Consultant @ Automatic Nation 
YouTube Channel 

Oooh, this sounds like a fun build. There’s a lot of potential complexity and I suppose it depends on how deep down the rabbit hole you want to go…

Ignoring the purchasing component for the moment, my head immediately goes to two tables, one for models/products and one for inventory. The model is N64, and then a linked record to inventory is to a specific N64 console you have in stock, where you can specify additional details (see through purple, including expansion pack upgrade, etc) and give each inventory object your own sku# if desired. 

I would probably keep consoles/games/controllers grouped together, and unless you’re planning to sell the AV/Power cords independently then I’d just lump them with the consoles myself. 

I could see a third table, “Purchases” where you document any supply purchases you make, the total cost, and then link that to your inventory table.

Anyway, those are scattered thoughts, I haven’t built a fully specced inventory management system in AT myself, so take my thoughts with a slight grain of salt, but this sounds like fun!

Thanks for the input.

So it sounds like both of you are suggesting a single table for products with a bunch of nullable columns for values that don’t apply to a specific product:
 

and then a junction table to handle the bundling:

and finally a bundle table to handle prices etc.

Does that make sense?

The only other thought I have right now is that because each item is used I don’t generally consider multiple copies to be the same product. For example even if I have 2 copies of Mario Kart one of them might have a torn label that would affect the resale value. Would that additional complexity change the approach at all or would you just consider each copy a different row in the product table?

Thanks again!!

This is the reason I would suggest an inventory table separate from a product table, so you can specify conditions on individual items. I whipped up a very basic version of what I’m thinking here (no purchase table).

I still dunno if you really need to list power cables as products unless your planning on selling them separately from the console. I would favor an additional field on the inventory table for listing required accessories and create a filter in your inventory for when you need said cables (I kinda prototyped that in my linked one with the status “In Prep”)

Just seeing your proposed tables here though, do play more with Airtable. You don’t need IDs in the way you’re expressing them here, as that isn’t how linkages are expressed. 

As for nullable values, you can create views that excise the blank fields to make it easier to input.

Thanks for following up. I didn’t grok what you meant originally but your example makes it really clear and would help with some issues I’ve encountered before.

I still dunno if you really need to list power cables as products unless your planning on selling them separately from the console. I would favor an additional field on the inventory table for listing required accessories and create a filter in your inventory for when you need said cables (I kinda prototyped that in my linked one with the status “In Prep”)

I agree and no I don’t sell them separately. A better example of this might be replacement joysticks. Controllers need them often and I have to buy them separately. I’d like to track how many I have and know whether or not one was added to a controller because it increases the resale value. I also don’t sell those separately. Any thoughts there?

Just seeing your proposed tables here though, do play more with Airtable. You don’t need IDs in the way you’re expressing them here, as that isn’t how linkages are expressed. 

Fair enough. Im more familiar with relational DB’s so yeah I have some learning to do :)

Again, I really appreciate your willingness to help.

Hmm, well you don’t really want to list those in your Inventory Table (trying to document every spare joystick you have would be tedious). It’s not within best practice for database design, but you could create a second linked record field to products showing only accessories…

Honestly if you want to tally this kind of stuff properly, then perhaps a “Components” table is in order, listing components that might be applicable to your inventory. I added a Components table to that example I linked earlier along with a cost rollup, should be pretty self explanatory. One rule of Airtable is that the answer is usually more tables haha.