mysql - 商店产品的类表继承 : How to place an order

Question

我正在尝试创建一个电子商务/商店系统，我决定在基于this great answer的SQL设计中选择类表继承设计。 .

现在我有以下表结构:

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

这适用于在网站上展示产品。但是，如果我想订购一些产品，这将如何运作？

如果我的产品有一个表，我可以将 product_id 作为外键分配给一个关系表，但是对于多个表，这似乎不再可能了。甚至可以使用类表继承吗？

我环顾四周，大多数答案/教程似乎都集中在产品的表示上，而不是客户的订单上。

Answer 1

从 product_shirt 和 product_ring 中删除字段 product_id，并使它们的 id 字段为主键 和 product.id 的外键。

您的 order_item 表将包含指向 product.id 的外键。

当您需要提取给定订单中有关产品的信息时，只需使用连接到 product 进行查询。当您需要特定产品的完整详细信息时，还可以根据实际产品类型加入 product_shirt 或 product_ring。

---

例子:

-- 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