Before i get ahead of myself.. database design critque plllleeassee...

Scope of project:create simple browse down (+search) engine/site for various telecom and networking parts arranged by manufactuer then brand then part to show a list of vendors that carry said product arranged by price and reorderable..

In reading other posts, figure it might not be a bad idea to run down my little structure and some sample data..

ttProducts
prodID (autonumber)
item (m7310, Trunk Module)
model (NT8B75, VB-44210)
manuID (from ttManu, to identify manufacturer)
catID (from ttCat, to identify category)
photo (for addition of url to pic)

ttManu
manuID (autonumber)
manu (Nortel Networks - Norstar)

ttCategory
catID (auto)
category (category part may fit in, Phones, Cards, Voicemail..)

ttVendors
vendorID (auto)
company
address1
address2
city, state, zipcode, phone, phone800, fax, email, website, login, password, descrip, added_on, active

ttInventory the table to tie em all together
invID (auto)
prodID (from ttProducts)
vendorID (from ttVendors)
newprice (price for part new, if any)
refurbprice (price for part used, if any)
oosprice (price for part out of service, if any)
warranty
venddescrip (short description from vendor for said part)

ttFeedback
feedID (auto)
vendorID (identifies who feedback was left for)
feedback (comments)
dateleft
leftby

--------------------
whole point of project is simple easy browse down to part, then see list of vendors. User selects Maufacturer from home page, is presented with a list of categories for parts matching said manu. User selects cateogry and is presented with list of all parts matching manu and category. User clicks part and is presented with list of vendors, 1 to 3 prices (whatever vendor filled in for part when adding it to browse) plus shot descrip from vendor if they left one, if not, filled in with vendor main descrip, if none, call blank. List is presented with lowest NEW price first and may be reordered by lowest Refurb price and again by lowest OOS Price, but all data displayed remains the same.

user may alternativly use a single serach text box to query db and pull anything that matches what they typed in in price order from the inventory table..

Will that data seem at first glance to fit together ok?
Thanks so much for alllll the help i get here.. it's truely tremendous!!!

Ata glance it looks good

I do disagree with the though of using an AutoNumber (PK) for link tables (ttInventory), it opens you up to duplicate data. You could inadvertently add the same prodID and VendorID without knowing it, unless you program in data integrity. I perfer to use the DB for this.

I would do this

ttInventory the table to tie em all together
prodID (from ttProducts) (PK/FK)
vendorID (from ttVendors) (PK/FK)
newprice (price for part new, if any)
refurbprice (price for part used, if any)
oosprice (price for part out of service, if any)
warranty
venddescrip (short description from vendor for said part)

S-

Ok cool..
so you're saying ditch the invID from ttInventory and use the prodID as the primary key for the invnetory table right?

makes perfect sense pending the answer to that question..

Use both ProdID and VendorID as combined concatenated PK (two fields-one key)

S-

i c.. not sure how to set that up...

and will that effect that fact that more than 1 vendor will be shown for one product...

it shouldnt though should it, cause each pair would be unique.... prodID 1 & vendorID 2
prodID 1 & vendorID 3, etc....

the whole point at the end, is to show a list of vendors that carry the selected part... the parts themselves are obvioulsy in the product table, vendors over there.. so forth..

i ALSO have to plan for the fact that SOME parts won't have a model number at ALL.

Which DB are you using?





Yes

S-

sql 7, and i have no idea how to make it do that.. Im fairly new at it ..

I went into the db tonight, and started to make the primary key of the ttInventory table the prodID column, but once a column is a primary key, you can have no duplicates right?

and I already have some products in there, of which multiple rows have the same prodID, so I figured better to wait til i had this figured out.

To make things easier... i've decided to keep a screenshot of my table diagram around in case anyone needs it..
located here > http://www.teletechnology.net/db.gif - might have to tell explorer or whatever u use to view image full size.. it's big but only 26k

When in the design mode of the table Select To Rows (both Fields) at one time and then click on the PK toolbar button. This should allow you to specify both field, combined, are the primary key.

S-

I C I C......
Thanks so much dood! You guys are freakin awesome!

-- bows --