mysql - 订单和子订单的数据库设计

以前，我根据以下信息创建了这个数据库模式：一个订单有许多项，每个项可以属于多个订单。一个商品有一个标价，这个标价会随着时间的推移而变化。应记录客户在特定日期购买商品的价格，以便进行历史报告。

CREATE TABLE item (
    item_id INT AUTO_INCREMENT
    ,item_name VARCHAR(30)
    ,item_list_price DECIMAL(10,2)
    ...
    CONSTRAINT ... PRIMARY KEY (item_id)
);
CREATE TABLE `order` (
    order_id INT AUTO_INCREMENT
    ,order_status VARCHAR(30)
    ,customer_id INT NOT NULL
    ...
    ,CONSTRAINT ... PRIMARY KEY (order_id)
);
CREATE TABLE order_item (
    order_item_id INT AUTO_INCREMENT
    ,order_id INT NOT NULL
    ,item_id INT NOT NULL
    ,order_item_quantity INT
    ,order_item_cost DECIMAL(10,2)
    ,CONSTRAINT ... PRIMARY KEY (order_item_id)
    ,CONSTRAINT ... FOREIGN KEY (order_id) REFERENCES `order` (order_id)
    ,CONSTRAINT ... FOREIGN KEY (item_id) REFERENCES `item` (item_id)
);

然而，我注意到一个项可以有多个子项。例如，一个汉堡包项目可以有一个奶酪子项目、一个培根子项目等。据推测，每个子项目应该可以属于许多项目。奶酪可以放在汉堡包、意大利面等上。显然，不会有递归子项。
实现这一目标的最佳方法是什么？
这是一种不用继承的方法。创建一个子项表和一个子项桥表。item_子项表包含一个强制的FK-to-item，但可以为空的FK-to子项。在Order表中，链接到item_子项。这不是正确的数据库继承，但在一定程度上模仿了它。

CREATE TABLE item (
    item_id INT AUTO_INCREMENT
    ,item_name VARCHAR(30)
    ,item_list_price DECIMAL(10,2)
    ...
    ,CONSTRAINT ... PRIMARY KEY (item_id)
);
CREATE TABLE subitem (
    subitem_id INT AUTOINCREMENT
    ,subitem_name VARCHAR(30)
    ,subitem_list_price DECIMAL(10,2)
    ...
    ,CONSTRAINT ... PRIMARY KEY (subitem_id)
);
-- Note how subitem_id is nullable
CREATE TABLE item_subitem (
    item_subitem_id INT AUTO_INCREMENT
    ,item_id INT NOT NULL
    ,subitem_id INT
    ,CONSTRAINT ... PRIMARY KEY (item_id, subitem_id)
    ,CONSTRAINT ... UNIQUE (item_subitem_id)
    ,CONSTRAINT ... FOREIGN KEY (item_id) REFERENCS item (item_id)
    ,CONSTRAINT ... FOREIGN KEY (subitem_id) REFERENCES subitem (subitem_id)
);

-- Note how this bridge table between order and item_subitem links to item_subitem's unique key
CREATE TABLE order_item_subitem (
    order_item_subitem_id INT AUTO_INCREMENT
    ,order_id INT
    ,item_subitem_id INT
    ,order_item_quantity INT
    ,order_item_cost DECIMAL(10,2)
    ,CONSTRAINT ... PRIMARY KEY (order_item_subitem_id)
    ,CONSTRAINT ... FOREIGN KEY (order_id) REFERENCES `order` (order_id)
    ,CONSTRAINT ... FOREIGN KEY (item_subitem_id) REFERENCES item_subitem (item_subitem_id)
);

这是处理这种情况的最好方法吗？什么会更好？
最终用户应该能够检查其历史购买记录（记住，标价在未来可能会发生变化）。以下是一个订单示例：

Order # 123456
Customer Name - Matthew Moisen
- Salad        $3
- Custom
   - Hamburger $5
   - Cheese    $1
   - Bacon     $1
- Apple        $1
-----------------
Total         $11

最佳答案

你所描述的是所谓的“捆绑”、“组合”或“营销包装”。
你可以卖一种产品，或者一包产品。您应该在这里使用抽象/继承。

--using class table inheritance. postgresql syntax (sorry, but it's less verbose).

-- a product is an abstract thing you can sell, ex good, combo, service, warranty.
create table products (
  product_id int primary key
);

create table goods (
  product_id int primary key references products(product_id),
  name text not null unique
  ...other good columns
);

create table bundles (
  product_id int primary key references products(product_id),
  name text not null unique
  ...other bundle columns
);

--map products to bundles:
create table bundle_products (
  bundle_id int references bundles(product_id),
  product_id int references products(product_id),

  primary key (bundle_id, product_id)
);

您需要使用关系除法（没有余数）来防止重复的捆绑包。使用触发器。

--order line item points at products now:
create table line_items (
  order_id int references orders(order_id),
  display_order smallint not null default 0,
  product_id int not null references products(product_id),
  quantity int not null default 1,
  unit_price decimal(19,2) not null,

  primary key (order_id, display_order)
);

关于mysql - 订单和子订单的数据库设计，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22312900/