我的建议结合了@ValNik的评论和@Daniele的回答:
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)
);