I am trying, for the first time, to make a inventory database.
 
That said I am looking for: 
1. Location table, to say where something is 
2. Bin table, to say what bin contains item and ties back to locations 
3. Parts table, to say what part it is, cost, price, description, part number and ties to what Bin its in, and what location has it. 
4. Inventory, to say how many are in each bin and ties to parts and bin. 
5. Users, for people that can access it and 2 levels, 1 as User and 1 as Admin 

I want to make a website to add and change data and maybe an app for a phone. 
Ability to scan a barcode to do inventory would be nice as well. 

So my questions: 
1. my fkey for each of the tables should be the same type? like Vchar(10) then I can do the same name in each of the tables that I want to tie? 

so... 
LocationID - PK 
Location - FK tied to BinLoc in Bin Table 
BinID - PK 
BinLoc - FK to Location 
BinNum - FK to Part and inventory 

So what are some ideas on how to tie this together?
[font="verdana, arial, helvetica, sans-serif; font-size: 12px; line-height: 23px"] 

Thank you Srtie4k I appreciate your time and efforts with me.
 
So I have created the DB with the following 4 tables.
 
CREATE TABLE `Inventory` (
`InventoryID` int(4) NOT NULL,
`LocationId` int(4) DEFAULT NULL,
`BinNumber` varchar(5) DEFAULT NULL,
`PartId` int(4) DEFAULT NULL,
PRIMARY KEY (`InventoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `Location` (
`LocId` int(4) NOT NULL,
`Active` set('A','I') DEFAULT 'A',
`Type` varchar(45) DEFAULT NULL,
`Name` varchar(45) DEFAULT NULL,
`Description` varchar(45) DEFAULT NULL,
`BinNumber` int(4) DEFAULT NULL,
PRIMARY KEY (`LocId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `Part` (
`PartId` int(4) NOT NULL,
`PartNumber` varchar(15) DEFAULT NULL,
`PartDesc` varchar(45) DEFAULT NULL,
`Weight` decimal(4,2) DEFAULT NULL,
`Cost` decimal(4,2) DEFAULT NULL,
`Price` decimal(4,2) DEFAULT NULL,
`Update` datetime DEFAULT NULL,
PRIMARY KEY (`PartId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `Users` (
`UserId` int(4) NOT NULL,
`Active` set('A','I') DEFAULT 'A',
`First` varchar(45) DEFAULT NULL,
`Last` varchar(45) DEFAULT NULL,
`Phone` varchar(15) DEFAULT NULL,
`Level` set('U','A') DEFAULT 'U',
PRIMARY KEY (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 

No Bin table?
 
I'm not sure exactly what your end goal is, but you should look into the process of normalization. If you can understand the concepts behind it, it will help you break down your system into it's component parts (i.e. tables) and the relationship between each of them.
 
It looks like you're on the right track, but you might need to normalize further to avoid possible update, insertion, and deletion anomalies. 

Going to add some data in so I can try to do a view or something and see if that helps me figure out my relationships better
 
 

Great point, thank you!
ok inserted this, and now looking at your suggestions.
INSERT INTO `OSHInv`.`Bin`
(`BinId`,
`BinNumber`,
`Quantity`)
VALUES
('0001','1A','25')
INSERT INTO `OSHInv`.`Inventory`
(`InventoryID`,
`BinNumber`)
VALUES
('0001','1B')
INSERT INTO `OSHInv`.`Location`
(`LocId`,
`Active`,
`Type`,
`Name`,
`Description`)
VALUES
('0001','A','Warehouse','Main','Primary Warehouse');
INSERT INTO `OSHInv`.`Part`
(`PartId`,
`PartNumber`,
`PartDesc`,
`Weight`,
`Cost`,
`Price`)
VALUES
('0001','SPK023','Platinum Sparkplugs','.02','2.66','6.56');
INSERT INTO `OSHInv`.`Users`
(`UserId`,
`Active`,
`First`,
`Last`,
`Level`)
VALUES
('0001','A','Greg','Arhart','A');

So here is how its sitting now.
I think it would be the inventory goes in the bin and the bin goes into the location
so would it be Inventory.BinNumber FK -> Bin.BinNumber, Location.BinNumber?
or wont that work?
I currently have no FK set yet. So I can modify as needed easily.
 
 
CREATE TABLE `Bin` (
`BinId` int(4) NOT NULL AUTO_INCREMENT,
`BinNumber` varchar(5) DEFAULT NULL,
`Quantity` int(5) DEFAULT NULL,
`Updated` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`BinId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `Inventory` (
`InventoryID` int(4) NOT NULL AUTO_INCREMENT,
`LocationId` int(4) DEFAULT NULL,
`BinNumber` varchar(5) DEFAULT NULL,
`PartId` int(4) DEFAULT NULL,
PRIMARY KEY (`InventoryID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `Location` (
`LocId` int(4) NOT NULL AUTO_INCREMENT,
`Active` set('A','I') DEFAULT 'A',
`Type` varchar(45) DEFAULT NULL,
`Name` varchar(45) DEFAULT NULL,
`Description` varchar(45) DEFAULT NULL,
`BinNumber` int(4) DEFAULT NULL,
PRIMARY KEY (`LocId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `Part` (
`PartId` int(4) NOT NULL AUTO_INCREMENT,
`PartNumber` varchar(15) DEFAULT NULL,
`PartDesc` varchar(45) DEFAULT NULL,
`Weight` decimal(4,2) DEFAULT NULL,
`Cost` decimal(4,2) DEFAULT NULL,
`Price` decimal(4,2) DEFAULT NULL,
`Update` datetime DEFAULT NULL,
PRIMARY KEY (`PartId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

I think if I were setting this schema up, I'd do it this way:
 
 

In MySQL, it's in the Data Modeling section. You can create an EER Model from either a diagram or create a diagram from existing or new tables.

like so?
 

James_L

XrayMan
 
That should solve the OP's problem.      ;)

Especially months later after it's more than likely resolved.

The OP never did say if it's resolved, but we can deduce that the OP has either solved the problem, or just gave up on it.       ;)

No, never got it resolved. Feel like I am maybe out of my depth here.
I want to do this for my friend, 1 to help him out and 2 to learn something new and fun.
I'm the solo it guy for the most part. So I can only devote so much brain juice at a time. Hehe

I will look at the new updates provided and see if I can get further.

I did ask a friend to quote this and he came back with 25k. Blink.

ill chalk it up to gave up on this for now. I just don't have the time to devote to it. Thought I could create something simple to do this, but I just don't have the SQL skills to do this.
 
thanks for the help though, and sorry to have wasted anyones time on this. 
 
Maybe when I can create a basic one I can come back and ask again for some advice.