mysql - Database Design for Orders and SubOrders -

previously, made database schema following information: 1 order has many items, , each item can belong many orders. item has list price, can change on time. price @ customer bought item on particular day should captured 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 attention, however, item can have multiple subitems. example, hamburger item have cheese subitem, bacon subitem, etc. presumably, each subitem should able belong many items. cheese can go on hamburger, pasta, etc. apparently, there no recursive sub items.

what best way implement this?

here 1 way without using inheritance. create subitem table, , item_subitem bridge table. item_subitem table contains mandatory fk item, nullable fk subitem. in order table, link item_subitem. isn't proper database inheritance, mimics 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 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 bridge table between order , item_subitem links 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 best way handle situation? better?

an end user should able check historical purchases (keeping in mind list price can change in future). here example of 1 order:

order # 123456 customer name - matthew moisen - salad        $3 - custom    - hamburger $5    - cheese    $1    - bacon     $1 - apple        $1 ----------------- total         $11 

what describing called "bundle", "combo" or "marketing package".

you can sell 1 product, or package of products. should use abstraction/inheritance here.

--using class table inheritance. postgresql syntax (sorry, it's less verbose).  -- product abstract thing 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 columns );   create table bundles (   product_id int primary key references products(product_id),   name text not null unique   ...other bundle columns );  --map products 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 use relational division (no remainder) prevent duplicate bundles. use trigger.

--order line item points @ 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) );