/*顧客マスタ*/ 
create table consumer_mst (
 consumer_no int not null auto_increment,
 consumer_name varchar(255) not null,
 consumer_kana varchar(255) null,
 insert_at datetime not null,
 update_at datetime not null,
 delete_at datetime null,

 primary key(
consumer_no
 )
);

/*商品マスタ*/ 
create table commodity_mst (
 commodity_no int not null auto_increment,
 commodity_name varchar(255) not null,
 unit_price decimal not null,
 stock int not null,
 insert_at datetime not null,
 update_at datetime not null,
 delete_at datetime null,

 primary key(
commodity_no
 )
);

/*明細テーブル*/ 
create table details_tbl (
 details_no int not null auto_increment,
 consumer_no int not null,
 insert_at datetime not null,
 update_at datetime not null,
 delete_at datetime null,

 primary key(
details_no
 )
);

/*明細サブテーブル*/ 
create table details_sub_tbl (
 details_no int not null,
 commodity_no int not null,
 unit_price decimal not null,
 quantity int not null,
 total_price decimal not null,
 insert_at datetime not null,
 update_at datetime not null,
 delete_at datetime null,

 primary key(
details_no
,commodity_no
 )
);