database design for creating an inventory management application
I have created an inventory management tool for the company I work for but it is all based on a flat table in MySQL. (I am new to database design) I know that this is bad design so I have tried to make it more efficient by breaking up the data into separate tables. I have a few questions about my proposed design that I was hoping someone might give me some feedback on.
These are the fields that appear on the entry form:
1. Should I include image_id columns in tblItems and write the code so that there are three SQL statements one after another on the insert record form? (eg - The first one inserts the the image (names) into tblImages that is auto incremented and automatically gets an unique id. Then the second SQL statement retrieves the IDs from tblImages and gets a variable assigned to it with php for the third SQL statement which inserts the the actual inventory item and assigns ID numbers taken from tblImages to an image ID column)
Or is it better to leave the image_ID columns out of tblItems and have two insert statements that insert the data for the item into tblItem and the image names into tblImages?
2. If the answer to question number one is the latter, then how would I do a join that could properly associate an item with an image if I wanted to display a recordset?
3. Do you have any general suggestions on the design?
Any advice would be greatly appreciated. Thanks in advance.