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.