not sure how to structure my db

michaewlewis

I'm building a simple database for inventory where I work and I wanted to get some feedback and suggestions on a database schema for the project.
Currently I have one table, and I'm thinking more tables would help with the flow of things.
The table is called instock and has a date, description, part#, serial#, #used, used for, used at, initials, comments, totalUsed, and totalStock.
I'm thinking that I should break up the tables by categories (e.g. computers, pc parts, plc parts, radio parts, office supplies, legacy parts, etc.) but am not sure how to implement it. How should I do it?
I'm also wondering if I should have separate tables for in stock and used stock, but I think I can just use a binary field to say if something is in stock. The problem with that though is if part of what is recorded is in stock...... ???

Any and all feedback will be helpful. Thanks.

Piranha

It sounds like a good idea to split that table into more than one. I suggest that you read up on database normalization to understand what you are about to do, and help you how to design the database. On phpbuilder.com there is a good article about it.

When you have read it come with a design and let us come with critique to that.

laserlight

In particular, PHP Builder has an article on Database Normalization and Design Techniques.

michaewlewis

thanks, I just printed it out. I'll come back when I've learned more. 🙂

michaewlewis

Ok, after several hours of modifications and reading through that tutorial, I think I've got it.
If anyone wants to take a look at the attached file and let me know if the database schema is "normalized", it would be greatly appreciated. I attached the file as txt, just rename it as csv and open it in excel for a better view.

Please let me know if there are any changes that may need to be applied or if I'm
completely on the wrong track.
Thanks for the help.

rulian

everything looks great, i think you've structured this logically

is this a sort of equipment checkout/ checkin db?
if so then I wouldnt change anything, but if it's a product inventory db, I would maybe break out the instock / items out column for the product name table into perhaps the equipment table, so that different locations could have different amounts for the same product. But again, it depends on it's use

michaewlewis

it's going to be both a check in/out and an inventory database. I'm probably going to put all the inventory items into the database the same day (check in) and then as its used, check it out.
I'm not sure what you mean by "break out the instock / items out column..." Could you explain a little further?

rulian

in your nameIDAssoc table
you have quantityTotal, quantityInStock, quantityUse
associated with the product name

So if there will ever be the case that a product will have different quanity in either different location or catagory, then you will have to enter mutiple entries for the same product in this database. Not exactly a doomsday scenario but it will be a good idea that if this should be the case to put quantity into the equipment table. which means you have to enter an entry for different locations with the same product with different amounts, but it still keeps your nameIDassoc table from having duplicate information.

michaewlewis

I'm tracking the information based on the serial number in mainTable though because the serial number is the only thing that will change in every item. It should be impossible to have a duplicate serial number. With that said, why again would it be the best thing to put the quantity in mainTable? Seems like that would create duplicate entries.

rulian

it might not be the best thing, it was just a though, since I dont really know how products will be distributed in actualilty, I just saw that the same product in the nameIDassoc would need to have multiple entries for the same product, if their quantities should ever be different. if that is not the case, then how you have it is fine.

michaewlewis

thank you for your input 🙂