Tile Store Inventory System in progress db

gistfile1.sql

CREATE TABLE tiles (
	tile_id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
	tile_name VARCHAR(80) NOT NULL,
	quantity SMALLINT(5) UNSIGNED NOT NULL,
	old_lot_qty SMALLINT(5) UNSIGNED,
	n_height FLOAT(3) UNSIGNED NOT NULL,
	n_width FLOAT(3) UNSIGNED NOT NULL,
	n_thick FLOAT(3) UNSIGNED NOT NULL,
	e_height SMALLINT(5) UNSIGNED,
	e_width SMALLINT(5) UNSIGNED,
	local_supply TINYINT(1) NOT NULL,
	rating TINYINT(1) UNSIGNED NOT NULL,
	date_last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	tile_notes TEXT,
	PRIMARY KEY (tile_id)
	);

CREATE TABLE manufacturer (
	manufacturer_id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
	manufacturer_name VARCHAR(80) NOT NULL,
	PRIMARY KEY (manu_id)
	);

CREATE TABLE series (
	series_id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
	series_name VARCHAR(80) NOT NULL,
	man_id SMALLINT(3) UNSIGNED NOT NULL,
	PRIMARY KEY (series_id)
	);

CREATE TABLE material (
	material_id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
	material_name VARCHAR(30) NOT NULL,
	PRIMARY KEY (material_id)
	);

CREATE TABLE colour (
	colour_id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
	colour_name VARCHAR(30) NOT NULL,
	PRIMARY KEY (colour_id)
	);

CREATE TABLE sealant (
	sealant_id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
	sealant_name VARCHAR(60) NOT NULL,
	PRIMARY KEY (sealant_id)
	);

CREATE TABLE country (
	country_id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
	country_name VARCHAR(40) NOT NULL,
	PRIMARY KEY (country_id)
	)

CREATE TABLE colour_associations (
	colour_assoc_id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
	tile_id INT(6)  UNSIGNED NOT NULL,
	colour_id SMALLINT(3) UNSIGNED NOT NULL,
	primary_colour TINYINT(1) UNSIGNED NOT NULL,
	PRIMARY KEY (colour_assoc_id)
	);
	
CREATE TABLE manufacturer_associations (
	manufacturer_assoc_id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
	tile_id INT(6)  UNSIGNED NOT NULL,
	manu_id SMALLINT(3) UNSIGNED NOT NULL,
	PRIMARY KEY (manufacturer_assoc_id)
	);

CREATE TABLE series_associations (
	series_assoc_id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
	tile_id INT(6)  UNSIGNED NOT NULL,
	series_id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (series_assoc_id)
	);

CREATE TABLE material_associations (
	mataterial_assoc_id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
	tile_id INT(6)  UNSIGNED NOT NULL,
	material_id SMALLINT(3) UNSIGNED NOT NULL,
	PRIMARY KEY (material_assoc_id)
	);

CREATE TABLE sealant_associations (
	sealant_assoc_id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
	tile_id INT(6)  UNSIGNED NOT NULL,
	sealant_id SMALLINT(3) UNSIGNED NOT NULL,
	PRIMARY KEY (sealant_assoc_id)
	);

CREATE TABLE country_associations (
	country_assoc_id INT(6)  UNSIGNED NOT NULL AUTO_INCREMENT,
	tile_id SMALLINT(5)  UNSIGNED NOT NULL,
	country_id SMALLINT(3) UNSIGNED NOT NULL,
	PRIMARY KEY (country_assoc_id)
	);