Hi I’ve created a databases with 3 tables customer, products, orders.
I want to insert 1 order that can have mulitple products. So for example a customer may want to order coats, sunglasses and shoes in one order. At the moment I’m only allowing one product for an order.
How can I do this but show in orders table that the products are part of the same order. Not just seperate orders. So you can see actually see that a customer purchased multiple products in one order?
I think the confusion is that this is not “one to many” but “many to many”, because one order may have multiple products, but also one product can be in multiple orders.
So the answer would be another look-up table matching order IDs to product IDs.
You may need to split up your Orders Table into two. The first one will contain your orders_id(pk) and cust_id, the other will contain your ordered products.
I highly recommend using lowercase column names and avoiding prefixes. There isn’t any need to name the primary key product_id. Using id is fine and is a known convention. That goes for all tables id = primary key, simple. I also recommend adding the price to each line item in the order for historical purposes. Otherwise when prices of products change it will be difficult to calculate the price that was paid for items in older orders.
I think the idea was to store the price at the time of the order in the orders table, because the price may change over time, but what the customer paid at the time will not alter.
So you will still want the current price to remain in the products table, which won’t necessarily be the same as the price recorded on the orders table.
