Jump to content

Car Parts Database Structure


zealer

Recommended Posts

Hi all,

 

I would like some input on database structure for a website that will sell car parts. Necessary fields are:

  • Manufacturer
  • Part type (eg: exhaust, brakes, engine etc)
  • Part number
  • Description
  • Year & make of vehicle it fits (may need to be separate fields, I don't know, this is my main conundrum) **

 

Keep in mind, this is a basic example and purely hypothetical at this moment. I haven't done any coding yet.

My main roadblock at the moment is figuring out how to engineer part compatibility in conjunction with a search. Many parts fit many different vehicles. For example, part A123 could fit:

  • 1999-2004 Jetta 2.0
  • 1999-2004 Golf 2.0
  • 1999-2004 Passat 1.9

In your opinion, what would be the most efficient way to store and retreive matching parts? Would I have each model as a separate row? i.e:

row 1: 2002-2004 Jetta ... part A123

row 2: 1999-2004 Golf... part A123

row 3: 1999-2004 Passat... part A123

 

Basically I would have many duplicate rows where only the year and vehicle model changes. This would definitely work, but would eventually lead to a very large and messy database.

There are already companies that offer a service such as this, but I am looking at looking to sell less products and secondly, in a different language. At the moment, I'm trying to figure out the best way that I could do it. Once I have an idea in mind, then I can decide whether I want to go ahead with that or use a pre-made commercial platform.

Link to comment
Share on other sites

Frank_b

 

The requirement is 

  • which part fits which model and for which years for that model
  • to avoid  "many duplicate rows where only the year and vehicle model changes. This would definitely work, but would eventually lead to a very large and messy database".

so to find exhaust for a 2010 Golf

SELECT p.part_code
    , p.partname
FROM model_part mp
    JOIN model m USING (model_id)
    JOIN part p USING (part_id)
    JOIN part_type pt USING (part_type_id)
WHERE m.name = "GOLF"
    AND pt.description = "EXHAUST"
    AND "2010" BETWEEN mp.from_year AND mp.to_year

Although, in practice, you would have dropdowns to select type and model and use the IDs in the query

Edited by Barand
Link to comment
Share on other sites

Hi Barand, your insight has been invaluable. The diagram you posted is the best answer you could possibly give.

One other area that may need to be addressed is engines, as vehicles of the same year often come with different engines. I'm thinking another relational table could be used.

 

I'll admit, at this point I am leaning towards a commercial solution (so many cars, and so many parts... So many variables). They've done the legwork and is probably well worth paying the monthly fee, I will just have to figure out the language issue.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.