Structuring tables for data

[]{ronic]

Hey,

 

I have am creating a web app to hold customer and job details.. I have the following table structures to hold the data:

 

CREATE TABLE Customers (
  customerID int(5) NOT NULL auto_increment,
  customer varchar(25) default NULL,
  contact varchar(25) default NULL,
  phone varchar(25) default NULL,
  email varchar(25) default NULL,
  address varchar(25) default NULL,
  PRIMARY KEY (customerid)
) ENGINE = INNODB;


CREATE TABLE Jobs (
  jobID int(5) NOT NULL auto_increment,
  customerID int(5) NOT NULL default '0',
  invoiceNumber varchar(25) default NULL,
  purchasOrder varchar(25) default NULL,
  orderDate datetime NOT NULL default '0000-00-00',
  dateRequired datetime NOT NULL default '0000-00-00',
  jobStatus varchar(25) default NULL,
  PRIMARY KEY (jobID)
) ENGINE = INNODB;


CREATE TABLE vinyl (
  vinylID int(5) NOT NULL auto_increment,
  jobID int(5) NOT NULL default '0',
  colour varchar(25) default NULL,
  font varchar(25) default NULL,
  size varchar(25) default NULL,
  fileLocation varchar(25) default NULL,
  PRIMARY KEY (vinylID)
) ENGINE = INNODB;


CREATE TABLE screenprint (
  screenprintID int(5) NOT NULL auto_increment,
  jobID int(5) NOT NULL default '0',
  colour varchar(25) default NULL,
  font varchar(25) default NULL,
  size varchar(25) default NULL,
  fileLocation varchar(25) default NULL,
  PRIMARY KEY (screenprintID)
) ENGINE = INNODB;


CREATE TABLE items (
  itemID int(5) NOT NULL auto_increment,
  jobID int(5) NOT NULL default '0',
  supplier varchar(25) default NULL,
  code varchar(25) default NULL,
  colour varchar(25) default NULL,
  style varchar(25) default NULL,
  total varchar(25) default NULL,
  dateOrdered varchar(25) default NULL,
  PRIMARY KEY (itemID)
) ENGINE = INNODB;


CREATE TABLE itemsqty (
  itemqtyID int(5) NOT NULL auto_increment,
  itemID int(5) NOT NULL default '0',
  jobID int(5) NOT NULL default '0',
  size varchar(25) default NULL,
  quanity int(5) NOT NULL default '0',
  PRIMARY KEY (itemqtyID)
) ENGINE = INNODB;


CREATE TABLE embroidery (
  embroideryID int(5) NOT NULL auto_increment,
  jobID int(5) NOT NULL default '0',
  code varchar(25) default NULL,
  stitchCount varchar(25) default NULL,
  quanity int(5) NOT NULL default '0',
  PRIMARY KEY (embroideryID)
) ENGINE = INNODB;

Are these tables set out correctly for the collection of data needed. Also.. am I best to use Foreign Keys? My understanding of using foreign keys is not in place for queries as such, more to keep the tables clean of miss matched records etc?

 

Thanks

Edited May 28, 2014 by ]{ronic