create table users (
  user     VARCHAR(255),
  password VARCHAR(255),
  firstname VARCHAR(255),
  lastname VARCHAR(255),
  address1 VARCHAR(255),
  address2 VARCHAR(255),
  postalzip VARCHAR(10),
  city     VARCHAR(255),
  provstate VARCHAR(255),
  phonemain VARCHAR(10),
  phonealt VARCHAR(10),
  PRIMARY KEY (user)
);

create table tradeable_items (
  itemid     INT AUTO_INCREMENT,
  brandid    INT,
  categoryid INT,
  make       VARCHAR(255),
  model      VARCHAR(255),
  goodvalue  VARCHAR(20),
  fairvalue  VARCHAR(20),
  brokenvalue VARCHAR(20),
  PRIMARY KEY (itemid),
  CONSTRAINT FOREIGN KEY (brandid) REFERENCES item_brands (brandid),
  CONSTRAINT FOREIGN KEY (categoryid) REFERENCES categories(catid)
);

create table user_trades (
  itemid INT AUTO_INCREMENT,
  user VARCHAR(255),
  estvalue VARCHAR(255),
  finalvalue FLOAT,
  status VARCHAR(100),
  CONSTRAINT FOREIGN KEY (itemid) REFERENCES tradeable_items(itemid),
  CONSTRAINT FOREIGN KEY (user) REFERENCES users(user)
);

create table item_brands (
  brandid INT AUTO_INCREMENT,
  name VARCHAR(255),
  PRIMARY KEY (brandid)
);

create table categories (
  catid int AUTO_INCREMENT,
  name VARCHAR(255),
  PRIMARY KEY (catid)
);