MySQL – What to Store in Orders or Product_Order

MySQL

I am building my first e-commerce website and this is my current (relevant) database schema.

e-commerce database

I am very confused about what to store in the orders table or in the pivot table (order_product).

I read that you should store a copy of the single_price in the pivot table because the price may change at a later date. If this is correct should i also store copies of product-details in my order_product table ? Because those values may change like the price? . If a user changes their Shipping or Billing Address it should change for the next orders. This should work with the current setup or will it not?

Thanks in advance

Edit

My table names follow a naming convention from Laravel (5.2). This is the reason why my joining tables (pivot tables) have these names. The convention states that pivot tables should be the singular table names in alphabetical order.

Best Answer

In the Orders (Or Order_Header) table, you should store the Orders header - i.e. Order_ID, Client_ID (FK to Client table), Order_Date... &c.

You may want to have a trigger to update an Order_Total field for the total charge/cost. This is not strictly necessary, since you can query that from the Order_Detail table (see below).

A total may be useful if you apply discounts to an entire order (10% above sum X, say, or between dates or ad-hoc), rather than to individual products.

Order_product (I think that Order_Detail is a better, more descriptive, name) should be a joining/linking table between Orders and Product with fields Order_ID (FK), Product_ID (FK), quantity, price, tax, discount, charged_price per item and total for that item in that order.

To answer your question, the individual Product price will remain the price applying at Order_Date and not change subsequently (unless you apply a discount). You can't later refer back to the Product table for price since, as you say, it may change and customers don't like surprises (unless they're discounts :-) )

The Product_price (for a given order) is only valid at the time of that order and not subsequently, therefore it becomes "fixed" at that date. This is why, when calculating Order_total, you either refer to the Order_total (in the Orders table) or the Product_price (in the Order_detail table).

The Product_price field is useful at the date of the order or possibly for providing a sum of the value of current stock for example.

You might like to take a look here (search for "e-Commerce" and "shop" - eCommerce is just commerce after all - and "customers"). I've found this site helpful in the past as a starter.

Finally, for your schema naming, choose singular or plural table names (my preference is singular - with the exception of the word "order", an SQL keyword), but choose one and stick to it.

A final word of advice, if you haven't gone far down the road into this project, I would strongly urge you to consider using PostgreSQL instead of MySQL - it is a vastly superior database product and you (and your customers) will be thankful in the long run.

Best of luck with your project.

Related Question