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
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.