Access Database Structure Question

[alan1203]

Hi. I'm trying to develope a database to keep track of spare parts in my department and i'm not to sure how best to structure it. What I have just now is 4 tables:

tblManufacturer:
ManufacturerID: (PK)
Manufacturer

tblModel:
ModelID: (PK)
Model

tblSupplier:
SupplierID (PK)
Supplier:
Address1:
Address2:
Address3:
PostCode:

tblStockItems:
ManufacutererID: (FK)
ModelID: (FK)
PartDescription:
PartNumber: (PK)
PartPrice:
SupplierID: (FK)
Drawer:
Compartment:

This design would work fine for me if each part was for only 1 kind of machine but unfortunately each part can be used in different machines.
Preferably I would like to have only 1 record for each part and link it to a number of different models.
I would be grateful for any suggestions on the best way to make this work.

Thanks in advance for any help!
Alan

 

[PWise]

Add two tables
Tlbmachine
machineidpk)
machineName

tlbmachineParts
machinePartsidpk)
machineidfk)
PartNumber: (fk)

 

[lespaul]

check out the fundamentals document linked below for more on the rules of database design.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[JoeAtWork]

Is Model equivalent to a machine? If so, I would go with PWise's suggestion except you wouldn't need the Tblmachine table because that is already covered by the Model table. And in tblmachineParts replace MachineID with ModelID.

tblmachineParts is a junction table that lets you set up a many-to-many relationship between machines and parts.

[tt]
Models tblMachineParts Parts
ModelID+------------+ModelID |----+PartID
PartID+----------|
[/tt]