Database Design for Orders and SubOrders

Question

Previously, I made this database schema from the following information: One Order has many items, and each Item can belong to many Orders. An Item has a list price, which can change over time. The price at which a customer bought an item on a particular day should be captured for historical reporting.

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

It came to my attention, however, that an Item can have multiple Subitems. For example, a Hamburger Item could have a Cheese Subitem, a Bacon Subitem, etc. Presumably, each Subitem should be able to belong to many Items. Cheese can go on a hamburger, pasta, etc. Apparently, there will be no recursive sub items.

What is the best way to implement this?

Here is one way without using inheritance. Create a subitem table, and a item_subitem bridge table. The item_subitem table contains a mandatory FK to Item, but a nullable FK to Subitem. In the Order table, link to item_subitem. This isn't proper database inheritance, but mimics it to a degree.

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

Is this the best way to handle this situation? What would be better?

An end user should be able to check his historical purchases (keeping in mind the list price can change in the future). Here is an example of one order:

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

Answer 1

What you are describing is called a "bundle", "combo" or "marketing package".

You can sell one product, or a package of products. You should use abstraction/inheritance here.

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

You need to use relational division (no remainder) to prevent duplicate bundles. use a trigger.

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