Auto Parts Database

[MIKE3335]

I have tables set up for this like this.

tbl_car
CarID
CarName

tbl_Category
CategoryID
CategoryName

tbl_parts
PartID
CarID
CategoryID
PartNumber
PartDescription
Engine
Image
ETC ETC ETC.

The trouble im having is with what I think is a many to many relationship.

For the most part one part will only relate to one car / category but there are a few exception parts for instance Air freshener. That will fit all cars and the customer im doing the site for wants that to be returned when a user searches for ALL 240SX as well as be returned when a user searches for all 300zx parts ....( I have drop down menus set-up for the search)

I need a solid design solution for an auto parts database that would have many to many relationships to avoid problems like this ... If it means re-writing the whole database so be it ... but I need to get this worked out.

What would be the layout of an auto parts database where ONE PART could relate to many cars, and One Car could have many parts, and One Car could have many engines

I hope this makes enough sense and that someone can offer a few solutions .....

thanks in advance
Mike

 

[mas5]

I think what you need to resolve this is an extra table.

Keep you car table. It should have info just on the car. The parts table should have info just on the part. These two tables (car and part) should not be related to each other. Then you need a car_parts table. The car table has a one to many relationship with the car_parts table and the parts table has a one to many relationship with the car parts table. That table may just be limited to the CarID and the PartID.

Hope this helps.

 

[MIKE3335]

Good point and ( I think ) I see how that works....... but I cant see how I would set up the update/AddPart webpages to be able to update and add records to it.

If you have any tips or could explain how the process works that would be great.

Thanks

Mike

 

[mas5]

I'm not really sure if I understand how you want this to work, but this is what I would suggest:

A form for Parts. This form would populate the parts table. Within the parts form add a subform that would allow you to relate parts to cars. This subform would retrieve data from the car-parts table and would also allow the user to add cars to the parts. The car field in the subform should be a combo box that retrieves car data from the car table.

The another form for Cars. This form would populate the cars table. Within the cars form add a subform that would allow you to relate cars to parts. This subform would retrieve data from the car-parts table and would also allow the user to add parts to the cars. The part field in the subform should be a combo boxs that retrieves parts from the parts table.

You can get really fancy and not limit your combo box to list and if the user typed in a car or part (depending on which subform they were in) that didn't exist you could pop them into another form (or window) that would allow them to enter the car (or part) so they wouldn't have to keep switching back and forth.

I haven't created any webpages with Access so I'm not sure how the subforms work within webpages.

I think this gives your users the most flexibility as they can see the relationship online from the cars perspective and from the parts perspective. Let me know if this helps. My email notification doesn't seem to be working, but I'll check here again.