CREATE TABLE producttype(
	typeid		BIGINT AUTO_INCREMENT PRIMARY KEY NOT NULL,
	typename	CHAR(64) NOT NULL,
	INDEX(typeid)) type=InnoDB;

CREATE TABLE productdata(
	typeid		BIGINT NOT NULL,
	valueid		BIGINT AUTO_INCREMENT PRIMARY KEY NOT NULL,
	UNIQUE(valueid),
	typevalue	CHAR(64) NOT NULL,
	INDEX(typeid,valueid),
	FOREIGN KEY(typeid) REFERENCES producttype(typeid)
		) type=InnoDB;

CREATE TABLE manufacturer(
	mid BIGINT AUTO_INCREMENT PRIMARY KEY NOT NULL,
	mname CHAR(64) NOT NULL,
	INDEX(mid)
		) type=InnoDB;

CREATE TABLE parts(
	pid		BIGINT AUTO_INCREMENT PRIMARY KEY NOT NULL,
	mid		BIGINT,
	typeid		BIGINT NOT NULL,
	valueid		BIGINT,
	notes		CHAR(255),
	INDEX(pid),
	INDEX(mid),
	INDEX(typeid,valueid),
	FOREIGN KEY(mid) REFERENCES manufacturer(mid),
	FOREIGN KEY(typeid,valueid) REFERENCES productdata(typeid,valueid)
		) type=InnoDB;

DROP PROCEDURE IF EXISTS add_producttype;
DROP PROCEDURE IF EXISTS add_productdata;
DROP PROCEDURE IF EXISTS add_manufacturer;
DROP PROCEDURE IF EXISTS add_part;

DELIMITER //
CREATE PROCEDURE add_producttype(
	itypename	CHAR(64),
	OUT otypeid	BIGINT)
BEGIN
	INSERT INTO producttype(typename)VALUES(itypename);
	SET otypeid=LAST_INSERT_ID();
END //

CREATE PROCEDURE add_productdata(
	itypeid		BIGINT,
	itypevalue	CHAR(64),
	OUT ovalueid	BIGINT)
BEGIN
	INSERT INTO productdata(typeid,typevalue)VALUES
		(itypeid, itypevalue);
	SET ovalueid=LAST_INSERT_ID();
END //

CREATE PROCEDURE add_manufacturer(
	iname		CHAR(64),
	OUT omid	BIGINT)
BEGIN
	INSERT INTO manufacturer(mname)VALUES(iname);
	SET omid=LAST_INSERT_ID();
END //

CREATE PROCEDURE add_part(
	imid		BIGINT,
	itypeid		BIGINT,
	ivalueid	BIGINT,
	inotes		VARCHAR(255),
	OUT opid	BIGINT)
BEGIN
	INSERT INTO parts(mid,typeid,valueid,notes)VALUES
		(imid,itypeid,ivalueid,inotes);
	SET opid=LAST_INSERT_ID();
END //

DELIMITER ;

DELETE FROM parts;
DELETE FROM productdata;
DELETE FROM producttype;
DELETE FROM manufacturer;

CALL add_manufacturer("INTEL",@INTEL);

CALL add_producttype("Card",@CARD);
CALL add_productdata(@CARD,"8-Bit ISA",@ISA8);
CALL add_productdata(@CARD,"16-Bit ISA",@ISA16);
CALL add_productdata(@CARD,"EISA",@EISA);
CALL add_productdata(@CARD,"PCI",@PCI);
CALL add_productdata(@CARD,"AGP",@AGP);

CALL add_producttype("SDRAM",@SDRAM);
CALL add_productdata(@SDRAM,"PC66",@PC66);
CALL add_productdata(@SDRAM,"PC100",@PC100);
CALL add_productdata(@SDRAM,"PC133",@PC133);

CALL add_producttype("SIMM 72",@SIMM72);
CALL add_producttype("SIMM 30",@SIMM30);

CALL add_part(NULL,@SDRAM,@PC66,
	"Assorted Sizes",@PC66_ASSORTED);
CALL add_part(NULL,@SDRAM,@PC100,
	"128MB",@PC100_128);
CALL add_part(NULL,@SDRAM,@PC133,
	"128MB",@PC133_128);
CALL add_part(NULL,@SIMM72,NULL,
	"8MB",@SIMM72_8);