Warehouse 13: The DataBase the heart of Warehouse 13

As the title says my MySQL Data Base is the heart of the Warehouse system. Everything is just fluff, that helps the user(s). As this is a new system I don't have any web interface at this point, but I have the engine, more or less, well defined. So far I have described 4 tables, Categories, Manufactures, Info, History, and Spots. I will describe each of the tables.

Categories has four(4) columns:

CatID bigint(20) NO PRI NULL

CatName varchar(100) NO NULL

CatSeq bigint(20) YES NULL

Note varchar(500) YES
Manufacturers has Six (6) columns:

ManID	bigint(20)	NO	PRI	NULL	auto_increment
ManName	varchar(100)	NO		NULL
DivOf	bigint(20)	YES		NULL
Alive	enum('NULL','YES','NO','BOUGHT')	YES		NULL
Logo	varchar(200)	YES		NULL
PhoneNumber	varchar(20)	YES		NULL
Cage	varchar(6)	YES		NULL

Info has thirteen(13) columns:

InfoID	bigint(20)	PRI		auto_increment
CatID	bigint(20)
CatSeq	bigint(20)
ManID	bigint(20)
PartNumber	varchar(50)
Description	varchar200)
AltNumber	varchar(100)
DateInfo	timestamp		current_timestamp()
DateInfo	varchar(15)
BarCode	varchar(15)
Photo	varchar(1000)
WaitWeeks	int(11)
Note	varchar(1000)

Spots

SIDpot	bigint(10)	NO	PRI	NULL	auto_increment
Location	enum('Garage','Office','HallCloset')	NO		NULL
ShelfID	int(11)	NO		NULL
BlueBin	enum('O1','O2','O3','O4','O5','O6')	NO		NULL
SubSpot	varchar(6)	NO		NULL

The first Table is the Categories. Ok, this may seem daunting but I will help you out. It's not really that bad. First, you have to understand what a Category is, it is just a numerical representation of its name. A CatID and a parts CatSeq give you a unique number that can be used to reference the part. There are 129 unique Categories, and more will be added as needed.

Next is the Manufacturers table again we just assign a unique number to each manufacturer. this is done with a feature of MySQL which is auto_increment. Again DB likes numbers.

The next table is the heart of the thing it's called Info, again, the InfoID is an auto_increment. this is one of the main Keys. You will notice the CatSeq number currently this number is incremented by hand. But what should happen is that it is left blank and the system finds the Categories.CatSeq increments it and writes it in the record that was just stored. There is also a field labeled NSN which stands for National Stock Number. This would correspond to the CAGE Code field which stands for Commercial and Government Entity which the DLA (Defense Logistics Agency) assigns. My CAGE Code is 56L74.

You also have a History table which is more or less the inventory it also has a unique number HistoryID which includes things like the purchase number, invoice number, and other information about the item. But it does not tell you where the thing is and that is done in the next table Spots.

The Spots table tells you where the thing is. It has an enum which is the Location field, then SelfID, BlueBin SubSpot. For instance, let's say it's downstairs in the Garage. Its UniitID is C (third shelf on the left from the front), ShelfID is 2 which is the third shelf from the floor,

Spot is B (second from the right, and its SubSpot is 2 which is the third box from the bottom of the shelf. So its ID is Garage-C2B2.

Ok, so now you know roughly what my DB structure is. The only other tables that I could/will add are InvoiceHeader, InvoiceDetail, PurchaceOrder, and PurchaceOrderDetail. I know you will ask questions so please do ~Cris H.