Member Avatar for teedoff

Just curious, and well hoping to save myself months of time..lol

I've seen databases online already set up to purchase. I know a little about databases...very little, and could probably wing my way through creating a database for car lighting applications for the last 100 years, but thought there might be a database out there similar to what I need that I could download.

Anyone know of a database like this? If not, I'd like some feedback on how to set up the initial structure of the tables. In particular, what is most confusing me is that a single make and model of a car can in fact be associated with many years. I could have a manufacture table, ie chevy, ford, dodge, then have a makes table...such as camaro, f-150, challenger, but then I was thinking about how I would relate the last 40 years or so the camaro has been made.

Thanks for any tips and help.

  • 3 Contributors
  • 5 Replies
  • 2K Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by rch1231

Dani AI

Generated

Good instinct, . You do not want one giant table with year baked into the text. A clean approach is exactly what sketched: Make -> Model -> ModelYear, then hang your lighting applications off the specific Model+Year. That preserves referential integrity, lets you index well, and still handles models built across many years. Add trim/engine/body as optional attributes or a separate Qualifier table when you need finer granularity (mid-year changes, different bulbs by engine, market, etc.).

Here is a minimal starter schema and a lookup example you can run today:

-- core lookups
CREATE TABLE make (
  make_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE model (
  model_id INT PRIMARY KEY AUTO_INCREMENT,
  make_id INT NOT NULL,
  name VARCHAR(80) NOT NULL,
  UNIQUE (make_id, name),
  FOREIGN KEY (make_id) REFERENCES make(make_id)
);

CREATE TABLE model_year (
  model_id INT NOT NULL,
  year SMALLINT NOT NULL CHECK (year BETWEEN 1900 AND 2099),
  PRIMARY KEY (model_id, year)
);

-- parts and fitment
CREATE TABLE part (
  part_id INT PRIMARY KEY AUTO_INCREMENT,
  sku VARCHAR(64) NOT NULL UNIQUE,
  description VARCHAR(255)
);

CREATE TABLE fitment (
  part_id INT NOT NULL,
  model_id INT NOT NULL,
  year SMALLINT NOT NULL,
  position ENUM('low_beam','high_beam','fog','turn','brake','reverse') NOT NULL,
  notes VARCHAR(255),
  PRIMARY KEY (part_id, model_id, year, position),
  FOREIGN KEY (model_id, year) REFERENCES model_year(model_id, year)
);

Example query:

SELECT p.sku
FROM fitment f
JOIN model m  ON m.model_id = f.model_id
JOIN make mk  ON mk.make_id = m.make_id
JOIN part p   ON p.part_id = f.part_id
WHERE mk.name='Chevrolet' AND m.name='Camaro'
  AND f.year=2005 AND f.position='low_beam';

Two practical data sources and standards notes: NHTSA’s vPIC API can seed U.S. Make/Model/Year data for free if licensing is a concern. and API. And if you ever plan to exchange fitment with others, align your vocabulary with Auto Care’s ACES/PIES conventions (widely used in North America). See Auto Care’s overview of the standards and supporting databases (VCdb/Qdb/PCdb/PAdb). Auto Care Data Standards. ()

Recommended Answers

All 5 Replies

Member Avatar for rch1231

Well this may help. I found an Excel spread sheet on the web that has makes and models from 1990 to 2011 for just about every make and I can think of. There were several for sale but this one was free and you could import it quickly to mysql.

Member Avatar for teedoff

Thanks that does help. At least this gives me a list of every make and trim option for each year. And if this one exists, I'm sure there are spreadsheets of earlier years as well.

Again, the thing I'm worried about is how to normalize this type of database. I know I need a table with just the manufactures names, but then I had a table for just the makes of each manufactures. But the years are confusing me. Maybe I'm making it too complicated and should just have a table with all available makes with trims AND years together? I was thinking though this would not inforce referential integrity, right?

Member Avatar for adam_k

I'm sorry to break this to you, but I'm not sure that excel is free.
You may have landed on the download page, but it says "Thank you for purchasing" so I searched their site and found this page:

You might want to verify that this file is something you can use.

Member Avatar for teedoff

hmm, well I already downloaded it..lol

Either way, it may only serve as a reference for how many makes of vehicles there are in the time period 1990 to 2005, which is what that file contained. It may not be suitable for a database structure.

Member Avatar for rch1231

Hello,

That is interesting I got 1990 to 2010 and 21338 rows of data. Well if it would help I created a database with three tables from the data that may help. Make, Make_model and Make_model_year which seemed more logical to me. I exported the MySQL database and zipped it and you can download a copy at this URL:

http://txlinux.com/carmodels.zip

is my domain and you should be able to click on the link and down load it. Unzipped it is just a text file from mysqldump for the database. If you create a database and add USE <database_name>; to the from of the text (where database_name is the name of the database you created ) then load/run it as a query it will recreate the database I built indexes and all.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.