MySQL database Errors and Tips

MySQL database
#1

Hello,

As advise there seems to be a mistake in my database structure. If you found one kindly advise what is it and why is it a mistake. Also what is the alternative solution for it. Thank you.

[php]
– phpMyAdmin SQL Dump
– version 4.0.10deb1
– http://www.phpmyadmin.net

– Host: localhost
– Generation Time: Nov 01, 2016 at 09:34 PM
– Server version: 5.5.53-0ubuntu0.14.04.1
– PHP Version: 5.5.9-1ubuntu4.20

SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
SET time_zone = “+00:00”;

–
– Database: inventory

–
– Table structure for table department

CREATE TABLE IF NOT EXISTS department (
id int(11) NOT NULL AUTO_INCREMENT,
dept varchar(25) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

–
– Table structure for table hold_lot

CREATE TABLE IF NOT EXISTS hold_lot (
id int(11) NOT NULL AUTO_INCREMENT,
lot_id int(11) NOT NULL,
proc_type int(11) NOT NULL,
sub_id int(11) NOT NULL,
start_time datetime NOT NULL,
end_time datetime NOT NULL,
hold_reason varchar(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

–
– Table structure for table item_desc

CREATE TABLE IF NOT EXISTS item_desc (
item_code int(11) NOT NULL AUTO_INCREMENT,
item varchar(50) NOT NULL,
uom varchar(10) NOT NULL,
proc_type tinyint(4) NOT NULL,
item_class varchar(10) NOT NULL,
PRIMARY KEY (item_code),
KEY item_code (item_code)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=42 ;

–
– Table structure for table lot

CREATE TABLE IF NOT EXISTS lot (
lot_id int(11) NOT NULL AUTO_INCREMENT,
proc varchar(5) NOT NULL,
petsa varchar(6) NOT NULL,
serye int(11) NOT NULL,
date_create date NOT NULL,
PRIMARY KEY (lot_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=43 ;

–
– Table structure for table ng_logs

CREATE TABLE IF NOT EXISTS ng_logs (
id int(11) NOT NULL AUTO_INCREMENT,
lot_id int(11) NOT NULL,
proc_type int(11) NOT NULL,
sub_id int(11) NOT NULL,
ng_id int(11) NOT NULL,
qty int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

–
– Table structure for table no_good

CREATE TABLE IF NOT EXISTS no_good (
id int(11) NOT NULL AUTO_INCREMENT,
ng_name varchar(50) NOT NULL,
barcode_num int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

–
– Table structure for table process

CREATE TABLE IF NOT EXISTS process (
proc_type int(4) NOT NULL AUTO_INCREMENT,
process_code varchar(10) NOT NULL,
process_name varchar(50) NOT NULL,
def_qty int(11) NOT NULL,
fg int(11) NOT NULL,
PRIMARY KEY (proc_type)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

–
– Table structure for table qa_tact_time

CREATE TABLE IF NOT EXISTS qa_tact_time (
id int(11) NOT NULL AUTO_INCREMENT,
lot_id int(11) NOT NULL,
start_time datetime NOT NULL,
end_time datetime NOT NULL,
result int(11) NOT NULL,
qa_id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

–
– Table structure for table quantity_stock

CREATE TABLE IF NOT EXISTS quantity_stock (
qs_id int(11) NOT NULL AUTO_INCREMENT,
lot_id int(11) NOT NULL,
no_more_sw tinyint(1) NOT NULL,
item_code int(11) NOT NULL,
stock_qty int(11) NOT NULL,
price int(11) NOT NULL,
currency varchar(10) NOT NULL,
manufacturer varchar(50) NOT NULL,
exp_date date NOT NULL,
date_acquired datetime NOT NULL,
PRIMARY KEY (qs_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

–
– Table structure for table quantity_usage

CREATE TABLE IF NOT EXISTS quantity_usage (
qu_id int(11) NOT NULL AUTO_INCREMENT,
lot_id int(11) NOT NULL,
qs_id int(11) NOT NULL,
usage_qty int(11) NOT NULL,
PRIMARY KEY (qu_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=63 ;

–
– Table structure for table sub_process

CREATE TABLE IF NOT EXISTS sub_process (
sub_id int(11) NOT NULL AUTO_INCREMENT,
process_name varchar(50) NOT NULL,
process_id int(11) NOT NULL,
line_up int(11) NOT NULL,
PRIMARY KEY (sub_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

–
– Table structure for table tact_time

CREATE TABLE IF NOT EXISTS tact_time (
id int(11) NOT NULL AUTO_INCREMENT,
lot_id int(10) NOT NULL,
sub_id int(11) NOT NULL,
proc_type int(11) NOT NULL,
start_time datetime NOT NULL,
end_time datetime NOT NULL,
operator_id int(11) NOT NULL,
input_qty int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;

–
– Table structure for table uom

CREATE TABLE IF NOT EXISTS uom (
uom_id int(11) NOT NULL AUTO_INCREMENT,
uom_name varchar(11) NOT NULL,
unit_abbr varchar(10) NOT NULL,
PRIMARY KEY (uom_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

–
– Table structure for table users

CREATE TABLE IF NOT EXISTS users (
id int(11) NOT NULL AUTO_INCREMENT,
user varchar(50) NOT NULL,
pass varchar(50) NOT NULL,
email varchar(50) NOT NULL,
type enum(‘admin’,‘ppc’,‘operator’,‘qa’,‘line_leader’) NOT NULL,
dept tinyint(4) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=95 ;

[/php]

#2

Firstly, you want a more descriptive name than just ‘id’. You changed it on some of the tables. The reason is for joins. ID is an ambiguous name.

I need some clarification on the purpose of some of the tables. lot and hold_lot for instance, no_good (not a good table name by the way), quantity_usage, tact_time and qa_tact_time. Don’t know what uom is.

#3

Thanks for the information. We are two persons working on this database. I mention that to him. I told him when we are doing join it is hard to identify the ‘id’. He replied it is ok which we are using dots. for example using hold_lot.id instead of hold_lot.hold_id. He explain that it is redundant typing such word. Those id with name are the one I made but those without or only ‘id’ he is the one who made those. Are there any instance we will have a problem if we pursue this type of naming?

[hr]

lot = this is where we create the LOT number in the production. We also call this Lot Traveler Sheet(LTS) number.
hold_lot = this is where we place the time of the lot how long it was hold by the Quality Assurance personnel.
no_good = a common abbv is NG. These are reject name that where identified in a lot.
quantity_usage = a table for information on how much parts were used.
tact_time = measure how much time was consumed during the process of a certain LTS.
qa_tact_time = same as tact_time but this only measure QA process. This is not included in a regular tact_time.
lastly
uom = this is the only name we can think for Unit of Measure ;D ;D ;D This is a table for Piece, Grams, Pound, etc…

Sponsor our Newsletter | Privacy Policy | Terms of Service