I have two tables:
Product
Columns: Product ID (PK), Product Name
Order
Columns: Order ID (PK), Order Name
I would like to store which Product Id for each order.
Is it better to create another table:
ProductOrder table:
Order ID (FK), Product ID(FK)
or add an additional column in the Order table:
Order ID (PK), Order Name, Product ID (FK)
Edit: Order can only contain one product.
The best approach depends on the nature of the relationship between Product and Order in your application. If there is any possibility of an order containing multiple products now or in the future, option 1 (ProductOrder table) is the better choice. It keeps your database design normalized and avoids potential issues if your requirements change in futuer. Option 2 (Order Table)can be a simpler and efficient solution at this moment but normalization often wins in most cases where scalability and flexibility are priorities!
My suggestion is a mix of @ValNik 's comment and @Daniele 's answer:
CREATE TABLE orders (
-- these columns, plus any needed at order level
order_id INT NOT NULL PRIMARY KEY
, cust_id INT NOT NULL FOREIGN KEY REFERENCES(customer)
, order_ts TIMESTAMP NOT NULL
, order_discount DECIMAL(10,2)
, order_pctoff DECIMAL(3)
);
CREATE TABLE orderitem (
-- these columns come to mind, there might be others - at item level
order_id INT NOT NULL FOREIGN KEY REFERENCES(orders)
, prod_id INT NOT NULL FOREIGN KEY REFERENCES(products)
, quantity INT NOT NULL
, item_discount DECIMAL(10,2)
, item_pctoff DECIMAL(3)
, PRIMARY KEY (order_id,prod_id)
);
