Database design

@loach That approach can still lead to problems.

You should have an OrderItem model that represents line items in an order (so a one-to-many relationship). You should then copy the product name and price (at least) to the OrderItem model. You could have a foreign key from the OrderItem model to the Product model, but don’t rely on this foreign key being present. If the product is soft-deleted or force-deleted in the future, then your application is going to break.

Similarly, if the product‘s name or price is updated, you don’t want that propagating through to orders, which is why I said you’ll need to copy the name and price to the OrderItem model. If a customer buys Product A at $9.50 today, but then two days later you change the price of the product to $9.00, then the customer’s order still needs to say they bought their product at $9.50. If you just use a foreign key and update the product’s price, the customer’s order is going to show a different price to what they actually paid.

Thanks for the tips. I will try to get my head around what you are saying. If it is not too much trouble could you show me the setup for all this?

I am confused as to what all the tables I need are with your approach. Do I still need the intermediate table etc. I was going by a diagram i found online. Getting into many to many relationships is a real weak point for me in database design.

That approach is new to me. Almost all of the diagrams or explanations l have seen lead to this same setup. I am assuming yours is different? The store the price in the OrdersDetails table

https://www.informit.com/articles/article.aspx?p=29963&seqNum=3

@loach What I explained isn’t too dissimilar to that article. They’ve just called their model OrderDetails instead of OrderItem.

The issue is relying on a product_id column in that table. If you update a product then those changes are going to appear in historical orders. You don’t want that.

Similarly, if you delete the related product at a later date, then your application is going to break when a customer tries to a view an order that contains a now-deleted product, because you’ll be trying to retrieve details like name from a record that no longer exists.

OK so what you are saying is just copy the Product price and name etc to what they call the OrderDetails table and drop the product_id from that table. Correct?

Thanks

You can still have a foreign key from the order items table to your products table. Just make it nullable and don’t rely on the related product for anything unless you want runtime errors.