Car Parts Database Structure

[zealer]

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.

[Barand]

this should get you started

 

 

Edited October 17, 2014 by Barand

[Frank_b]

Barand,

 

Can you explain why you put from_year and to_year in the join-table model_part and not in the model table? 

Edited October 17, 2014 by Frank_b

[Barand]

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 October 18, 2014 by Barand

[Frank_b]

Looks good Barand. I am still not sure if i should choose for more model records which sometimes only differ in periods or if i should use your model.

[zealer]

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.

[Barand]

As well as engine size you have many different body styles too (hatchback, coupe, saloon, convertible, estate). Then there's transmission types ...

 

As I said, my model was a starting point.