Design Help for Normalization

[khunter]

Hello all,
I have been tasked to create a database to track tooling information for my company. I have a solid idea on the process involved and would like some help getting my tables and relationships setup with normalization. I have noticed in the short time I have been learning access that normalization is the key to happiness.

Anyway here is what I have:

tblModel
*ModelID
ModelYear
ModelCustomer
ModelStartDate

tblParts
*PartNumber
ModelID
PartDescription
PartProcess

tblTools
*ToolNumber
ModelID
ToolSource
ToolComments
ToolComplete

tblProjects
*ProjectID
ModelID
ToolQtyNeeded
PartQtyNeeded

A short synopsis on how this should work:
A model is built from many different parts, these parts are sometimes (usually not always) associated with the tool that is used to build them. A tool could be used to make several different parts and some parts use several tools.
In addition to this, a model can use a different quantity of parts to build it and those parts can require different quantities of tools to produce them.

I have tried several different ways to relate these tables together, and would like to ask the braintrust that is this forums for any help they could give me.


Thanks again

 

[ByteMyzer]

From what I gather in your post, the tools more directly relate to the parts used for each model, rather than the model itself. In that case, it might be better to modify the design of tblTools thus:

tblTools
*ToolNumber
*PartNumber
ToolSource
ToolComments
ToolComplete

...and establish the one-to-many relationship to tblParts instead of tblModel.

See if this works for you.

 

[khunter]

After more thought, I came up with the attached relationships for my database. I think it will allow me more room to grow in the future. What do you pro's think? Is this a solid solution and a decent use of normalization?

 

[Pat Hartman]

It looks good to me. I don't know if it is just the picture but I would define primary keys in the relation tables. To create a multi-field pk, select 1 column and then while holding the cntl key select up to 9 more. When all the fields are selected, press the key icon on the toolbar.