Order Physical Database Model

• Image of the Physical Database Model
• DDL (MySql)

Image of the Physical Database Model

DDL (MySql)

Create table or_order (
	id Int NOT NULL AUTO_INCREMENT,
	status_code Varchar(5) NOT NULL,
	customer_id Int NOT NULL,
	order_date Date,
	ship_date Date,
	cretime Datetime NOT NULL,
	updtime Datetime NOT NULL,
 Primary Key (id)) TYPE = InnoDB
ROW_FORMAT = Default;

Create table or_order_status (
	code Varchar(5) NOT NULL,
	title Varchar(40) NOT NULL,
 Primary Key (code)) TYPE = InnoDB
ROW_FORMAT = Default;

Create table or_customer (
	id Int NOT NULL AUTO_INCREMENT,
	status_code Varchar(10) NOT NULL,
	name Varchar(40),
	shipping_address_id Int,
	billing_address_id Int,
	cretime Datetime NOT NULL,
	updtime Datetime NOT NULL,
 Primary Key (id)) TYPE = InnoDB
ROW_FORMAT = Default;

Create table or_address (
	id Int NOT NULL AUTO_INCREMENT,
	country_code Varchar(3) NOT NULL,
	line_1 Varchar(100),
	line_2 Varchar(100),
	city Varchar(100),
	region Varchar(100),
	cretime Datetime NOT NULL,
	updtime Datetime NOT NULL,
 Primary Key (id)) TYPE = InnoDB
ROW_FORMAT = Default;

Create table or_product (
	id Int NOT NULL AUTO_INCREMENT,
	sku Varchar(20) NOT NULL,
	name Varchar(20) NOT NULL,
	cretime Datetime NOT NULL,
	updtime Datetime NOT NULL,
	UNIQUE (sku),
 Primary Key (id)) TYPE = InnoDB
ROW_FORMAT = Default;

Create table or_order_detail (
	id Int NOT NULL AUTO_INCREMENT,
	order_id Int NOT NULL,
	product_id Int NOT NULL,
	order_qty Int,
	ship_qty Int,
	cretime Datetime NOT NULL,
	updtime Datetime NOT NULL,
 Primary Key (id)) TYPE = InnoDB
ROW_FORMAT = Default;

Create table or_customer_status (
	code Varchar(10) NOT NULL,
	title Varchar(20) NOT NULL,
 Primary Key (code)) TYPE = InnoDB
ROW_FORMAT = Default;

Create table or_country (
	code Varchar(3) NOT NULL,
	name Varchar(100) NOT NULL,
 Primary Key (code)) TYPE = InnoDB
ROW_FORMAT = Default;


Alter table or_order_detail add Index IX_or_orddet_order (order_id);
Alter table or_order_detail add Foreign Key (order_id) 
      references or_order (id) on delete  restrict on update  restrict;
Alter table or_order add Index IX_fk_order_orderstatus (status_code);
Alter table or_order add Foreign Key (status_code) 
      references or_order_status (code) on delete  restrict on update  restrict;
Alter table or_order add Index IX_fk_order_customer (customer_id);
Alter table or_order add Foreign Key (customer_id) 
      references or_customer (id) on delete  restrict on update  restrict;
Alter table or_customer add Index IX_fk_cust_billaddr (billing_address_id);
Alter table or_customer add Foreign Key (billing_address_id) 
      references or_address (id) on delete  restrict on update  restrict;
Alter table or_customer add Index IX_fk_cust_shipaddr (shipping_address_id);
Alter table or_customer add Foreign Key (shipping_address_id) 
      references or_address (id) on delete  restrict on update  restrict;
Alter table or_order_detail add Index IX_fk_ordd_product (product_id);
Alter table or_order_detail add Foreign Key (product_id) 
      references or_product (id) on delete  restrict on update  restrict;
Alter table or_customer add Index IX_fk_cust_customerstatus (status_code);
Alter table or_customer add Foreign Key (status_code) 
      references or_customer_status (code) on delete  restrict on update  restrict;
Alter table or_address add Index IX_fk_addr_country (country_code);
Alter table or_address add Foreign Key (country_code) 
      references or_country (code) on delete  restrict on update  restrict;