Database setup advice

Need help Database
1

So I own a screen printing and embroidery biz and i’m tinkering around with making my own software to make life a little easier. New to bubble so bare with me.

Just need some direction on setting up the database. As you can see I have your typical invoice, but from here i’d like to be able to click the add garment link to pop up the add a product image you see. In here is where I would like to search for a model number and have it populate what colors and sizes are available in this particular model number. Once I have entered in how many of each size it will essentially take that row that has quantities and add it to my invoice which from there I can then calculate a price.

image
image1415×499 63.7 KB

What confuses me the most besides it all is that for example the 29M model number is available in black in sizes S, M, L, XL as shown in the image below, but then the color ASH is available in S, M, L, XL, XXL, 3XL … I need to populate my Add a product pop up with those inputs so I know that that particular color is available in say a XXL or not.

Below is the csv file from my supplier of all the items I can hopefully import one day. Do I just replicate my database after this?

image
image1357×389 194 KB

2

I would think having two tables …

One for Product (so name, brand, id etc) which has a List of …

Variations (so size, colour, quantity etc).

3

Thank you @NigelG for taking the time to respond. I noted that database layout you mentioned and will def give it a whirl. My main question is how do I get the small and medium inputs to show up after I enter a model number? These sizes are unique to this model “PC54” so another model number could offer sizes small through XL. I know how to list out things in a RG but not sure how to setup the database to show that the model PC54 is only available in small and medium.

image
image516×739 15.7 KB

4

In your product page, you can Filter the Product’s Variations List where quantity > 0

So you may be duplicating “Small, Blue” across a number of small, blue, products that that means the Variations are unique for each product.

5

Hi Sales8
Just came across your thread (I acknowledge it is a few years old)
I also run a small embroidery/print shop and am tinkering with the idea of creating my own management software I would be really interested in any updates on your project

Did you complete the software looking at the screenshots it looks pretty slick

Anyway looking forward to any feedback you can provide
Thank you, Declan (Based out of Ireland)