Database Normalisation

[lupercale]

I have a problem with many to many relationships. I WISH TO CREATE A DB OF car parts. The fields are

Part_ID
Part_name
Part_description
Year_ID (for car)
Make_ID (for car)
Model_ID (for car)

There is a many to many relationship between the car parts and the car model.

Each part can be used in many cars

each car can use multiple parts.

How do I create tables to reflect this.

[nemonoman]

What you want to do is
1. Separate your data entities
2. Create RESOLVING tables

Example
table
CarModel
-modelID
-modelName
etc..

CarPart
-partID
-partName

etc
Store the relationship of Models and Parts in a RESOLVING TABLE
CarModelPart
-modelID
-partID

That's ALL the fields in the resolving table, basically.

Populate the CarModel table with info about car models

id Name
1 Civic
2 Element
3 Hybrid

Populate the CarPart table with car parts
1 Tires
2 Cheap Wheels
3 Expensive Wheels
4 Normal Battery
5 Hybrid Battery

Populate the CarModelPart table like this:
Model ID/Part ID
1, 1
1, 2
1,4
2,1
2,3
2,4
3,1
3,2
3,5

Now use a Join to put the parts and models together in a query:

SELECT modelName, partName
FROM CarModelPart
INNER JOIN CarModel ON CarModel.modelID=CarModelPart.modelID
INNER JOIN CarPart ON CarPart.partID=CarModelPart.partID
AND modelName = 'hybrid'

Produces:
Hybrid - Tires
Hybrid - Cheap Wheels
Hybrid - Hybrid Battery

etc.

[lupercale]

that has cleared things up. thank alot