Database setup for different product sizes

[joshdaman.duckworth]

Assistance needed

Need help on structuring my database. My app is a multi-vendor marketplace where vendors are able to sell their own products.

I want to be able to limit the quantity that a user can buy based on the amount of stock that a vendor has available. I don’t want the vendors to have to individually enter a SKU for each item that they want to sell on the platform, I want them to simply be able to select for e.g. UK10 and then say that they have 5 pairs of shoes in UK10’s available.

In the screen shot above. The user will select which size they want and then they will only be able to increase the quantity to the stock amount for that given product and size. I am currently using an option set for my shoe sizes and then adding the options to that specific product but I’m struggling to assign a quantity to that specific size.

Obviously willing to switch from using an option set for sizes if it gives me more interaction with the sizes. Also wanting to have different types of sizes for other products such as (XXS, XS, S, M, L) and a sizing for food products etc… Not sure on how to structure my database accordingly…

Thanks in advance for any help!
Josh

[joshdaman.duckworth]

@keith @andrewgassen @ashley.benson.tait @duke.severn

Any help appreciated please guys

[shpak.serhiy]

What if you create a Thing “Stock” with fields “quantity”, “product”, “size”?

[duke.severn]

@joshdaman.duckworth

I want to be able to limit the quantity that a user can buy based on the amount of stock that a vendor has available. I don’t want the vendors to have to individually enter a SKU for each item that they want to sell on the platform, I want them to simply be able to select for e.g. UK10 and then say that they have 5 pairs of shoes in UK10’s available.

Okay let’s do this. This is a really complex, and really important question, especially for newer Bubblers. I’m by no means an expert data architect, but I’ve made serious efforts to up my game in the field and this might help. Another disclaimer, there is a quasi-infinite quantity of ways to set this up, and a serious number of them will work. This is not THE way, merely A way.

Let’s start granular, and work backwards: I like to ask myself “what is the smallest, unique, thing that I can think of, that I, IN MY SITUATION, need to worry about?” For example, if you’re a shoelace manufacturer, your data architecture will look slightly different than the one in this example. The smallest thing might be an aglet… But we’re already digressing.

Working off your example, it is the product (a physical pair of shoes, along with the box they are in, whatever attributes you can physically attach to that unique thing). So now we have an idea of the “RECORD” we want to create. That “RECORD” will be tossed around, sure, but you want to select something that mostly always stays the same (i.e.: a pair of white Jordans, size xl). We can also add the things that are likely to change, such as order status, etc.

Ok, so what makes them different? What makes them “DIFFERENT” from others, aside from physically being a different pair than the identical one next to it? Size, color, brand, year, country etc. However, you can have two pairs of shoes (so UNIQUE) but that look identical. Generally, if you can make a thing that isn’t completely UNIQUE, but that does help differentiate between different RECORDS, you make a “FIELD”. In your case, this is easy. Usually, manufacturers already list that as a SKU. You type in that unique id, it gives you all of the attributes of the shoe that make that shoe DIFFERENT TO OTHER SHOES, but not UNIQUE TO THIS SHOE.

Uh oh, you can see where this is going. All SKU’s are DIFFERENT from each other. But UNIQUE shoes (RECORD) can have the same SKU. So following our thought architecture, a SKU is both a RECORD ANNNNNNND a FIELD??? Well, exactly. Now let’s attach a SKU to every UNIQUE pair of shoes. To do this, we can just add a “FIELD” called “SKU” to each UNIQUE RECORD of a shoe. Now when you pull any shoe’s RECORD (UNIQUE) you also pull a SKU (UNIQUE). This gives your shoe a lot more of a personality, let’s you quickly filter the shoes you have, the colors, the sizes, etc…

So you basically do this over an over again in your app, start granular, make sure all RECORDS will ultimately be UNIQUE. If you have a set of data that is UNIQUE, but can be shared across other things’ RECORDS, you make it a FIELD. If you did this right, your THING name will usually be what best represents a SINGLE RECORD (in your example, pair of shoes) .

As a recap, and to answer your question about quantity: you could do this many ways, but let’s go with transactional accounting approach. Your UNIQUE item enters the system somehow, and you now have a record for it. This is a SHOE, with the SKU#876382. There are many like it, but this shoe is UNIQUE. It also shares UNIQUE attributes with a whole whack of other shoes, all of the other RECORDS of shoes that have the same SKU. Okay, now somehow, because it will belong to a vendor (UNIQUE), you add a field to it and select the VENDOR it belongs to. Now you can calculate in real time how many shoes your vendor has. DO A SEARCH FOR “SHOES” who’s SKU is #876382 and who’s VENDOR is JOSH’S SHOESTORE COUNT. Josh has 6 shoes of that particular model, color, size, provenance, material, whatever.

You do this excercise over and over, essentially with two objectives in mind: eliminate redundancies (make your THINGS and FIELDS as UNIQUE as possible) and reduce the amount of data tied to a thing. What I mean is the computer in the background, when you say pull this “RECORD” of a USER, who has a list of ORDERS dating back 6 years, it will also pull ALL OF THOSE RECORDS. If you’re trying to pull 1000 users to find a particular one, you also pull perhaps hundreds of orders for each user each year and that’s quite a bit of data.

So maybe to make it less UNIQUE but more MANAGEABLE, you only keep the most recent 10 orders under USER, or orders within the last two months, or whatever you want. Then you have a THING called ORDER TABLE, with the FIELDS “USER” and “ORDERS”. When you neeeeeeeed to pull ALL of a particular user’s records, to search through them or change something, you’re pulling all of the data, but not other user’s data or order. When you’re just looking for their name or something, you won’t pull their records dating back six years.

Anyway I’m not so sure this was super clear, and not so sure I covered the question, so you let me know if this makes sense.

Unsolicited advice: read up on database architecture and keep reading if you don’t understand everything. Then go on Bubble and make something. Then read more, and restart. It’s the best way for me to learn, I recommend it.

[duke.severn]

@duke.severn

So you basically do this over an over again in your app, start granular, make sure all RECORDS will ultimately be UNIQUE. If you have a set of data that is UNIQUE, but can be shared across other things’ RECORDS, you make it a FIELD. If you did this right, your THING name will usually be what best represents a SINGLE RECORD (in your example, pair of shoes) .

Part II - because I reread your question and no I didn’t answer it.

Now you have a setup that lets you look up UNIQUE shoes, or UNIQUE SKUs, or UNIQUE vendors.

@joshdaman.duckworth

I want them to simply be able to select for e.g. UK10 and then say that they have 5 pairs of shoes in UK10’s available.

Ok so they have 5 pairs of shoes in UK10’s. How will you know if they are Jordans or Converse? Or do you just care that they have those sizes, and don’t care what color, model, manufacturer etc. they are? If you DO care, you MUST associate them to a SKU (or whatever concept of a SKU I outlined above) somehow. Maybe your vendor selects it from a list showing “normal” names? Do they pick “Nike” then “Jordans” then “Black” and you lookup a SKU for them? You can do that and create X new records of UNIQUE shoes with that SKU. But somehow, for your setup to work in the real world, you need to know or generate a SKU… Please refine what you’re suggesting because I have no idea how you go about generating a product with attributes beyond what you ask the user to input, or you’ve input yourself.

[joshdaman.duckworth]

Hi Duke,

Sorry for the late response, was very busy this week. Thank you so much for the in-depth response. I am going to try and put it all into practice this week and will definitely let you know where I run into difficulties.

Just one quick question for now, if you don’t mind answering. I recall you saying that is it important to not store too much data on one data point, so does that mean creating more ‘things’ and then referencing them as opposed to trying to have lots of data within one ‘thing’ / data point. In the attached screenshot, if I understand correctly, in the forum post about performance @josh advised against having lots of referenced ‘things’. Apologies I may have got my terminology a bit muddled up.

Thanks in advance.

[duke.severn]

Nope you got it. The issue is not HAVING many things, it’s referencing a thing to a thing (to a thing ands so on) and trying to pull that data. The issue is that instead of directly pulling the thing directly, you technically are pulling two or three things to get the one you need, which is redundant. Instead, you can use a single field in thing(n) and say do a search for Thing who’s [text field] is [value that will reference the correct thing]. I talk about it in a little more depth here:

Data architecture design in Bubble.is