Notion Answers

Help between Notion users


Register & Ask

It's free & easy

Get answers

Answers, votes & comments

Vote and select answers

Receive points, vote and give the solution

Question

1vote

How best to structure databases for barebones ERP solution?

I want to build a simple system for tracking inventory of parts. For this I have 4 key things:

A Part is just that - a screw, an electronic component, whatever. Something that I need to build a Product I need to keep track of how many of each Part I have at any given time and how many are committed to an order.

A Product is a collection of parts. Product 1 might take 1x Part 1, 5x part 2, and 10x Part 3 to build. I have a set of products that I sell, each of which requires a different combination of Parts.

A Part Order is an order that I place to replenish the stock of some parts. A Part Order might include 100x Part 1 and 50x Part 2. Part Orders add to the total number of the associated Parts.

A Product Order is an order placed by a customer that requires me to build the products in the order, and decrease the total number of the associated Parts that I have in stock.

I am having a hard time structuring this set of databases to interact effectively, and I am hoping for some guidance.

Right now I have 4 databases:

Parts: Each row is a different part number. Columns should include total count for each, which is (the sum of all associated Parts in Part Orders that have been marked as Arrived minus the sum of all associated Parts in Product Orders that have been marked as shipped), the total count incoming (the sum of all associated parts in Parts Orders that have not been marked as Arrived) the total count committed (the sum of all associated parts in Product Orders that have not been marked as shipped)

Products: Each row is a different Product. I need this to define which Parts, and how many of each, make up the total. I could do this by having a Number column for every Part, but this becomes unwieldy fast and doesn't scale well. I could have a relation column that lets me call out all of the Parts that make it up, but I do not see how I can keep track of the count of each part in the list. How would one do this effectively without needing a column for every part number?

Parts Orders: Each row should be a new order, and should include how many of each part were ordered. This needs to be structured in such a way that my Parts database can extract the information and add it all up. I can do this pretty easily if I only order one Part per row, but this is inefficient. How can I structure this to be able to include multiple different Parts per Part Order and still be able to extract the information about totals in the Parts database?

Product Orders: each row should be a new order that should include all of the Products orders. Again, I could have a number column for every Product and a calculated column for every part, but this scales very poorly. I could have a relation column that just lets me list out the Products in the order, but then I don't see how to keep track of counts of each, or how to break that out into Parts so that I can add them up for the Parts table.

How would you structure this? Any suggestions are most appreciated!

1 Answer

2votes

adam Points1460

I think this is what you're looking for? This should be 100% of the foundational structure, and I think 90% of your desired functionality (doesn't use shipped status to affect inventory values).

https://involvedesign.notion.site/Inventory-Management-System-IMS_0-5-8539bbca9727437ea0b9f3904709a29d

Let me know and I can adjust it as needed.

0vote

kbriggs commented

This is great! My only issue with this structure is that it scales poorly with number of parts - for example, if I have 100 parts to work with I need 100 columns for every order, most of which will be zero. I was hoping there was a way to make it more compact, for example, by being able to specify a list of parts and a list (comma-delimited?) list of numbers so that each Order is just two columns - but I do not see a good way to roll that up between databases.

2votes

adam commented

I don't think that's possible given how Notion works. This structure might take you 30-60 minutes to setup for 100 parts, but then is fully automated and for every customer order, you just select the products they've bought. Should be good enough :)

You might want to have things done separately anyways, as if you make one mistake with a comma in a csv format, everything goes to hell. This has some form of data resiliency built in.

0vote

kbriggs commented

Thanks for taking the time to assemble this and to clarify for me what's possible! I will start with this template and build on it.

0vote

adam commented

Sure thing! Bug me if you need more help. Also, let me know if you find a better way!

Please log in or register to answer this question.

...

Welcome to Notion Answers, where you can ask questions and receive answers from other members of the community.

Please share to grow the Notion Community!

Connect