Hi everyone,
I have the following table which associates products with orders placed on a website.
CREATE TABLE orders_products
( order_id INT
, product_code VARCHAR(10)
, price DECIMAL(10,4) NOT NULL
, vat DECIMAL(10,4) NOT NULL
, quantity SMALLINT NOT NULL DEFAULT 1
, notes VARCHAR(255)
, PRIMARY KEY ( order_id, product_code )
, CONSTRAINT orders_products_fk1 FOREIGN KEY ( order_id ) REFERENCES orders ( id ) ON DELETE RESTRICT
, CONSTRAINT orders_products_fk2 FOREIGN KEY ( product_code ) REFERENCES products ( code ) ON UPDATE CASCADE ON DELETE RESTRICT
);
When I want to add a product to an order, I run an insert statement like this.
INSERT INTO orders_products
( order_id, product_code, quantity, price, vat )
VALUES
( 1, 'PR-CODE', 1, 30, 6 )
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES ( quantity );
Let’s say I add run the above query, where price and vat match the equivalent fields found in the products table. All is fine.
Then I want to add the same product to the order, but because the customer is loyal, I’m giving them 10% off the list price. The price and vat obviously won’t match, but the query will only increment the quantity and the additional product will end up being added at the same price.
Two options immediately spring to mind. One is to modify the primary key thusly:
PRIMARY KEY ( order_id, product_code, price )
The other solution would be to add a further unique key in addition to the existing primary key:
UNIQUE KEY orders_products_index1 ( order_id, product_code, price )
I’m just wondering if anyone could advise me on which solution would work best! Obviously any further alternatives would be welcomed.
Many thanks in advance.