-1

I think it will help to give a little context, not only about the DB architecture but the actual product. I am building a business where we will be selling pallets of liquidated merchandise. Think a wooden pallet with 10-100 items (every item will have a scannable UPC) on it. Each of these items needs to be listed on the website but we are not actually selling those items. We will be selling the pallet as a single product to the customer.

So, you'll have a pallet table, a UPC table, and the join table UPC_pallet table to establish a many-to-many relationship.

Now, here is the tricky part. Other than the actual UPC codes, I have ZERO product information. That will have to be derived from a bulk API call to a third-party API. In most cases, it will be to Amazon's ASIN API since most of our product will be from Amazon anyway. This wouldn't be a problem if I was simply rendering the information on the frontend for visual purposes but I need more than that - I need the cumulative MSRP from the API call to determine my pallet price. I plan on taking a percentage of the MSRP and setting my price from that.

I am a little loss on how to do this smoothly... I could just determine the price in the front end, and pass it on through the checkout phase into the payment processor but that leaves me zero information on what I sold the item for. After confirmation of payment, I guess I can update the DB on the backend in the orders section and call it good. Is this an ugly approach?

I am word vomiting right now but I would love a little advice on how you guys would approach this. I am frontend dev (although I have done a lot of Fullstack work like most of us) but this stuff makes me a little uneasy

Improve this question
3
  • 1
    “I could just determine the price in the front end” – uh, nonono. If by “frontend” you mean “client's browser” then you absolutely can't trust the frontend. A buyer could manipulate the price they have to pay, and you wouldn't be able to detect this. You must decide on a price within your backend, which incidentally will also allow you to cache information from third party APIs. Commented Mar 25, 2022 at 17:49
  • How would this work with shipping? Just do shipping calculations in the backend and display them where we need to? I will have to dynamically generate the shipping dependent on the user's address (they'll pre-fill a lot of the logistical questions) and the pallet count. I guess I answered my own question. Sorry, a lot of this is me breaking intellectual ground in the process. I am brainstorming/requesting help :D. Commented Mar 25, 2022 at 19:13
  • 1
    Exactly, you'd send data back to the backend, do the relevant calculations there, and then tell the user about the result. If want to store data in the backend across requests, you can use a session cookie as an ID, provided that this cookie contains a cryptographically secure random value so that it can't be guessed/manipulated. Alternatively, techniques like JWT allow you to store data in the client. But since you cryptographically sign this data with a secret key, you can verify that it wasn't manipulated when it's passed back to the backend. Commented Mar 25, 2022 at 22:10

1 Answer 1

Reset to default
3

You will have to have your own prices table, even if it's just a cache of Amazon's, because as you say you need to know what you sold the item for. You will also want to consider how you cope with fluctuations in their price, unavailability of the API, mistakes (sudden zero prices?), and so on.

There are two approaches I would consider:

  • price the pallet in advance, into a pallet_price table, possibly with updates at regular intervals. This then becomes a simple e-commerce operation of "sell item with a price".

  • treat the pallet as a basket, possibly with hidden contents: keep track of all the items separately and aggregate the price whenever you need it. This may be necessary if you have to treat the items differently for sales tax purposes, etc.

(I would obviously do this in the backend, because if there's one thing the front end absolutely cannot be trusted to do it's calculate prices for anything other than display purposes! You don't want customers editing the hidden "price" field and then sending it to the payment processor!)

When placing an order in a shop, should a snapshop be taken of the products in an order also? has some very constructive discussion.

Improve this answer
2
  • Excellent suggestions! I am doing a "drive-by" comment due to lack of time. I will circle back with more follow-up questions. I do have one immediate question - do you think this type of functionality will result in my going custom all the way around (order/inventory/products/cart). Or can this dovetail into an existing solution? Commented Mar 25, 2022 at 17:24
  • The first of my suggestions would easily work with an existing solution. But either way I suggest you spend a bit of time familiarising yourself with some of the existing solutions (and there are a lot!) before deciding to write your own. Commented Mar 28, 2022 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.