How to store purchases "N of one product" in the table "Orders"

Question

So, we have our main tables for categories, products and product options.

categories
id | name | active | parent_id

Products
id | name | price | active

c_p_link
category_id | product_id

Options
id | product_id | price | price_override | active | stock

Which works great.

But I have two requests.

The first is how to structure orders.

We have an order table id | customer_id | ordered | status id | customer_id | ordered | status

And we also have an order_products id | order_id ..? table id | order_id ..? id | order_id ..?

This is what interests me. Say a customer orders 30 products. we

• Add 30 rows and add a price for each individual item in each row.
• Add one line, add the combined amount to the line
• Add one line, add an individual price to the line

The next part, later, we expect to add voucher support to the basket. for example 10% discount, buy two, get one free, etc. The overall design of this I have not fussed too much right now (this is at least a couple of months). but I wonder if this will affect which version of the order_products table I should select?

Answer 1

Disclaimer: I never wrote a database model regarding a “shopping cart” or “orders”

---

I think that the price at the time of purchase should be encoded in the purchase data: just like a paper receipt from the store. Let me call it total_price , which represents each detail line on the receipt and should not be confused with total_purchase_price .

That is a fixed amount. It does not matter if the price of the product changes later, and the price changes will not reflect how much was [paid].

Thus, I would have the following fields: product , unit_price , quantity , total_price . A calculated column, such as base_total_price ( unit_price * quantity ), can be easily added if required.

Now, total_price can be a calculated value based on the word base_total_price * precent_discount : but, no matter how it ends, I believe that total_price should exist and should be fixed at the time of purchase. (This means that if it is a calculated column, all inputs are also committed at the time of purchase.)

Addendum: As indicated above, I have never developed such a model before, but one thing that I observed in stores is discounts, which are used as a negative element with cost details. That is, items are bought "at full price", and then the registry adds an entry to offset the costs of each commercial. I do not know the merits / arguments of this approach.

Answer 2

just add the quantity to the order_products table :)

I prefer the third solution, I believe that this is best suited for the performance of your database.