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:

item name
category
type
age
description
image_lg
image_med
image_sm
bought_price
sell_price
bought_location
bought_date

This is the design I have come up with:

tblItem

item_id
item_name
item_age
item_desc
cat_id
type_id
sm_image_id
med_image_id
lg_image_id
bought_price
sell_price
bought_location
bought_date


tblImage

image_id
image_name


tblImageType

image_type_id
type_description


tblImageLink

item_id
image_id
image_type_id


tblType

type_id
type_name


tblCategory

cat_id
cat_name

My questions are as follows:

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.

zanshin