1

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.

1

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.

| improve this answer | |
  • Thanks for this answer. PostreSQL is not an option because i'm far into the project. The reason for my table names is because i am using Laravel. This is their naming convention. Joining tables always have the singular table names in alphabetical order. Which will do some automated work for you if you follow this convention. What i don't really understand is why i should store the total price . I will do the calculations with php code (this gives me more options when it comes to tax & coupons) . If i'm wrong about that can you explain why that is important? – Christophvh Apr 12 '16 at 13:27
  • 1
    Well, as I said, you may wish to apply "global" discounts to the global Order_total rather than to individual items. It also might help make your SQL a little less complex. But you are not wrong and neither am I. If you're happy doing tbis through code then fine. I would urge you though to perform as many data manipulation operations inside the database server as possible. Code (languages and frameworks) come and go, data (and databases) tend to endure. – Vérace Apr 12 '16 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.