create table widget_store_java.people (
  id integer unsigned not null auto_increment,
  first_name varchar(50) not null default '',
  middle_initial varchar(2),
  last_name varchar(50) not null default '',
  person_type varchar(10) not null default 'USER',
  password varchar(50) not null default '',
  salt varchar(50) not null default '',
  username varchar(50) not null default '',
  primary key(id)
)
engine = InnoDB;

create table widget_store_java.addresses (
  id integer unsigned not null auto_increment,
  street_1 varchar(255) not null default '',
  street_2 varchar(255),
  city varchar(50) not null default '',
  state varchar(2) not null default '',
  zip varchar(5) not null default '',
  zip_plus_4 varchar(4),
  person_id integer unsigned not null default 0,
  primary key(id),
  constraint fk_addresses_people foreign key fk_addresses_people (person_id)
    references people (id)
    on delete restrict
    on update restrict
)
engine = InnoDB;

create table widget_store_java.credit_cards (
  id integer unsigned not null auto_increment,
  cc_num varchar(20) not null default '',
  card_type varchar(10) not null default '',
  expr_dt varchar(10) not null default '',
  person_id integer unsigned not null default 0,
  primary key(id),
  constraint fk_credit_cards_people foreign key fk_credit_cards_people (person_id)
    references people (id)
    on delete restrict
    on update restrict
)
engine = InnoDB;

create table widget_store_java.orders (
  id integer unsigned not null auto_increment,
  address_id integer unsigned not null default 0,
  credit_card_id integer unsigned not null default 0,
  person_id integer unsigned not null default 0,
  total float not null default 0,
  primary key(id),
  constraint fk_orders_addresses foreign key fk_orders_addresses (address_id)
    references addresses (id)
    on delete restrict
    on update restrict,
  constraint fk_orders_credit_cards foreign key fk_orders_credit_cards (credit_card_id)
    references credit_cards (id)
    on delete restrict
    on update restrict,
  constraint fk_orders_people foreign key fk_orders_people (person_id)
    references people (id)
    on delete restrict
    on update restrict
)
engine = InnoDB;

create table widget_store_java.order_items (
  id integer unsigned not null auto_increment,
  order_id integer unsigned not null default 0,
  item_id integer unsigned not null default 0,
  quantity integer unsigned not null default 0,
  subtotal float not null default 0,
  primary key(id),
  constraint fk_order_items_orders foreign key fk_order_items_orders (order_id)
    references orders (id)
    on delete restrict
    on update restrict,
  constraint fk_order_items_items foreign key fk_order_items_items (item_id)
    references items (id)
    on delete restrict
    on update restrict
)
engine = InnoDB;

show tables;