MYSQL table structure

matrix_ · May 29, 2014, 4:36am

Hey,

I 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

r937 · May 29, 2014, 9:45am

probably, but we don’t really know what you’re going to collect

vinyl and screenprint have the same structure and could be merged (with an additional “type” column)

itemsqty should probably be merged into items

yes

matrix_ · May 30, 2014, 1:15am

I have done a restructure of the tables and combined vinyl and screen print…

But “itemsqty” I think is needed because for each item added to the “items” table could have several size’s and quantity’s… example: 5 x small, 3 x medium and 2x large for the item with itemID 1.

Also trying to get my head around foreign keys… I have made changes to the tables how I think they should be, but feel like there is an over kill in linking all the tables?

The idea is to collect the customer details in one table, then place job descriptions/items etc across the other tables making up the complete job sheet…

 
CREATE TABLE customers (
  customerID int(10) NOT NULL auto_increment,
  customer varchar(100) default NULL,
  contact varchar(100) default NULL,
  phone varchar(25) default NULL,
  email varchar(150) default NULL,
  address varchar(255) default NULL,
  PRIMARY KEY (customerid)
) ENGINE = INNODB;

CREATE TABLE Jobs (
  jobID int(10) NOT NULL auto_increment,
  customerID int(10) NOT NULL default '0',
  invoiceNumber varchar(25) default NULL,
  purchasOrder varchar(25) default NULL,
  notes text 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),
  FOREIGN KEY (jobID) REFERENCES customers(customerID)
) ENGINE = INNODB;

CREATE TABLE vinylScreen (
  vinylScreenID int(10) 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(150) default NULL,
  type varchar(25) default NULL,
  PRIMARY KEY (vinylScreenID),
  FOREIGN KEY (vinylScreenID) REFERENCES customers(customerID)
) ENGINE = INNODB;

CREATE TABLE items (
  itemID int(10) NOT NULL auto_increment,
  jobID int(10) NOT NULL default '0',
  supplier varchar(150) default NULL,
  code varchar(50) default NULL,
  colour varchar(25) default NULL,
  style varchar(25) default NULL,
  total varchar(25) default NULL,
  dateOrdered datetime NOT NULL default '0000-00-00',
  PRIMARY KEY (itemID),
  FOREIGN KEY (itemID) REFERENCES customers(customerID)
) ENGINE = INNODB;

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

CREATE TABLE embroidery (
  embroideryID int(10) NOT NULL auto_increment,
  jobID int(10) NOT NULL default '0',
  sequance varchar(100) default NULL,
  colour varchar(25) default NULL,
  PRIMARY KEY (embroideryID),
  FOREIGN KEY (embroideryID) REFERENCES customers(customerID)
) ENGINE = INNODB;

Cheers

r937 · May 30, 2014, 10:47am

your FKs are not right

in the jobs table, you have jobid and customerid, but then you reference jobid to the customer table – i’m guessing that should be customerid instead

same problem in the other tables

this is not “overkill” but i’m guessing just a misunderstanding of how FKs are supposed to work

matrix_ · June 9, 2014, 1:32am

Sorry, not really getting my head around this very fast Should the customerID be added to each table along with the jobID, then making the FK’

Thanks

matrix_ · June 9, 2014, 4:02am

Another attempt below… Should customerID be a field within all the job related tables as well?


CREATE TABLE customers (
  customerID int(10) NOT NULL auto_increment,
  customer varchar(100) default NULL,
  contact varchar(100) default NULL,
  phone varchar(25) default NULL,
  email varchar(150) default NULL,
  address varchar(255) default NULL,
  PRIMARY KEY (customerid)
) ENGINE = INNODB;

CREATE TABLE jobs (
  jobID int(10) NOT NULL auto_increment,
  customerID int(10) NOT NULL default '0',
  invoiceNumber varchar(25) default NULL,
  purchasOrder varchar(25) default NULL,
  notes text 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),
  FOREIGN KEY (customerID) REFERENCES customers(customerID)
) ENGINE = INNODB;

CREATE TABLE vinylScreen (
  vinylScreenID int(10) 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(150) default NULL,
  type varchar(25) default NULL,
  PRIMARY KEY (vinylScreenID),
  FOREIGN KEY (jobID) REFERENCES jobs(jobID)
) ENGINE = INNODB;

CREATE TABLE items (
  itemID int(10) NOT NULL auto_increment,
  jobID int(10) NOT NULL default '0',
  supplier varchar(150) default NULL,
  code varchar(50) default NULL,
  colour varchar(25) default NULL,
  style varchar(25) default NULL,
  total varchar(25) default NULL,
  dateOrdered datetime NOT NULL default '0000-00-00',
  PRIMARY KEY (itemID),
  FOREIGN KEY (jobID) REFERENCES jobs(jobID)
) ENGINE = INNODB;

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

CREATE TABLE embroidery (
  embroideryID int(10) NOT NULL auto_increment,
  jobID int(10) NOT NULL default '0',
  sequance varchar(100) default NULL,
  colour varchar(25) default NULL,
  PRIMARY KEY (embroideryID),
  FOREIGN KEY (jobID) REFERENCES jobs(jobID)
) ENGINE = INNODB;

John_Betong · June 9, 2014, 9:05am

Hi matrix,

Try searching for “Tutorial mysql tables normalization”?

Far better that a trial and error approach and will also be beneficial if you choose to add extra tables.

r937 · June 9, 2014, 11:18am

looking better!

interesting question

i don’t think it should be, but it could be

oh, and itemsqty table should have a FK to items

shophiarajan47 · June 10, 2014, 4:50pm

The problem is not just normalization and the use of Foreign key but also in the logic part. I don’t even get what you want to achieve with so much of redundant duplicate data.

r937 · June 10, 2014, 9:41pm

please give examples of what you feel is “redundant” or “duplicate”

matrix_ · June 11, 2014, 11:11am

Could be my lack of understanding… I am not selling common products, I am creating job sheets that will contain unique information for each job/client. My take from what I have read about normalization is that I cant break my table structures down anymore given there is no redundant duplicate data within tables below?


CREATE TABLE customers (
  customerID int(10) NOT NULL auto_increment,
  customer varchar(100) default NULL,
  contact varchar(100) default NULL,
  phone varchar(25) default NULL,
  email varchar(150) default NULL,
  address varchar(255) default NULL,
  PRIMARY KEY (customerid)
) ENGINE = INNODB;

CREATE TABLE jobs (
  jobID int(10) NOT NULL auto_increment,
  customerID int(10) NOT NULL default '0',
  invoiceNumber varchar(25) default NULL,
  purchasOrder varchar(25) default NULL,
  notes text 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),
  FOREIGN KEY (customerID) REFERENCES customers(customerID)
) ENGINE = INNODB;

CREATE TABLE items (
  itemID int(10) NOT NULL auto_increment,
  jobID int(10) NOT NULL default '0',
  supplier varchar(150) default NULL,
  code varchar(50) default NULL,
  colour varchar(25) default NULL,
  style varchar(25) default NULL,
  total varchar(25) default NULL,
  dateOrdered datetime NOT NULL default '0000-00-00',
  PRIMARY KEY (itemID),
  FOREIGN KEY (jobID) REFERENCES jobs(jobID)
) ENGINE = INNODB;

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

CREATE TABLE embroidery (
  embroideryID int(10) NOT NULL auto_increment,
  jobID int(10) NOT NULL default '0',
  sequance varchar(100) default NULL,
  colour varchar(25) default NULL,
  PRIMARY KEY (embroideryID),
  FOREIGN KEY (jobID) REFERENCES jobs(jobID)
) ENGINE = INNODB;

CREATE TABLE vinylScreen (
  vinylScreenID int(10) 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(150) default NULL,
  type varchar(25) default NULL,
  PRIMARY KEY (vinylScreenID),
  FOREIGN KEY (jobID) REFERENCES jobs(jobID)
) ENGINE = INNODB;

r937 · June 11, 2014, 11:39am

your understanding is fine

my last post was directed at shophiarajan47

shophiarajan47 · June 11, 2014, 6:00pm

Like the Jobid and customerID there are many which can be in only one table while other table need to get in normal forms. Vinyal

r937 · June 11, 2014, 7:06pm

i’m not sure you understand the concept of PKs and FKs

matrix_ · June 12, 2014, 12:07am

Yea I got that…

Another question… The fact I am using FK’s, am I able to delete a singe row from say the embroidery table without all other tables being affected?

Thanks

r937 · June 12, 2014, 12:13am

sure

but you might not be able to delete a job if it has embroidery rows, depending on the ON DELETE options declared for the embroidery FK

matrix_ · June 12, 2014, 1:10am

Ahh, so using fks means my general mysql queries be formatted differently?

r937 · June 12, 2014, 1:38am

no, not really

what specifically did you think might be affected?

matrix_ · June 13, 2014, 1:10am

Having it delete all the records across tables with FK relationship, when only one record from a single table is needed to be deleted…