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âŚ
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.
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.
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.
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.
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:
