Class table inheritance for shop products: How to place an order

Question

I am trying to create a e-commerce/shop system, I've decided to choose the class table inheritance design in SQL design based on this great answer.

Now I've got following table structure:

product ( id [PK], name, description )
product_shirt ( id [PK], product_id [FK],  color, size, stock, price ) 
product_ring ( id [PK], product_id [FK], size, stock, price )
// maybe coming more

This works for representation of the products on a website. But if I wanted to place an order, of a few products how would this work?

If I had one table for my products I could assign the product_id as foreign key to a relation table, but with multiple tables this seems not possible anymore. Is it even possible with class table inheritance?

I've looked around alot, most answers/tutorial seem to concentrate on the representation of the products, but not on the order of a customer.

Answer 1

Drop fields product_id from product_shirt and product_ring, and make their id fields both primary key and foreign key to product.id.

Your order_item table would contain a foreign key to product.id.

When you need to pull information about products in a given order, make a query with a JOIN to product only. When you need the full details of a specific product, also JOIN with either product_shirt or product_ring depending on the actual product type.

---

Examples:

-- red XL shirt: product #1
INSERT INTO product VALUE (1, 'Red XL shirt', 'What a lovely shirt');
INSERT INTO product_shirt VALUE (1, 'XL', 'red', 1, 12);

-- blue S shirt: product #2
INSERT INTO product VALUE (2, 'Blue S shirt', 'How nice a shirt');
INSERT INTO product_shirt VALUE (2, 'S', 'blue', 1, 12);

-- small ring: product #3
INSERT INTO product VALUE (3, 'Small ring', 'One to rule them all');
INSERT INTO product_ring VALUE (3, 'S', 1, 5);

-- customer orders a "Blue S shirt" and a "Small ring":
INSERT INTO order_item VALUES (
    1, -- order_number
    2, -- product_id
    1, -- quantity
), (
    1, -- order_number
    3, -- product_id
    1, -- quantity
);

-- overview of the basket
SELECT * FROM order_item
JOIN product ON product.id = order_item.product_id
WHERE order_number = 1;

-- details of the basket contents
-- -- this will only show details pertaining to products of type "shirt", if any
SELECT * FROM order_item
JOIN product ON product.id = order_item.product_id
JOIN product_shirt ON product_shirt.id = product.id
WHERE order_number = 1;

-- -- this will only show details pertaining to products of type "ring", if any
SELECT * FROM order_item
JOIN product ON product.id = order_item.product_id
JOIN product_ring ON product_ring.id = product.id
WHERE order_number = 1;

-- notice you cannot UNION these results as we expect a different number of columns
-- your application classes are expected to handle these differences