I am creating an app that needs all available US automotive make/models for each year. I have read several suggestions on other posts, like use Freebase or DbPedia or EPA, but those datasets all appear rather incomplete and inconsistent. I checked out open APIs like Edmond's, but they restrict storing their data - I need it in my db, so that doesn't work. Any suggestions where I can get this data without having to shell out money?
pnuts50.1k77 gold badges6464 silver badges102102 bronze badges
Car Makes & Models Database by Trim & Engine Automobiles categorized by auto / car make, model, trim, engine and year from 1942 to 2015 Description: This is an automobiles and cars database that sorts the vehicles / cars by 68 makes, 3,090 models, 8,832 trims, 19,830 engines across 74 years from 1942 to 2015.
Nate BarrNate Barr3,61822 gold badges2121 silver badges2121 bronze badges
closed as off topic by Andrew Barber, Bo Persson, Bill the LizardMar 6 '13 at 12:08
Questions on Stack Overflow are expected to relate to programming within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here. If this question can be reworded to fit the rules in the help center, please edit the question.
3 Answers
Apparently there is not much out there. And a lot of doubt that someone would be willing to provide such a repository. So I solved the problem myself, and am sharing my dataset with anyone else who finds themselves facing the same problem.
Nate BarrNate Barr3,61822 gold badges2121 silver badges2121 bronze badges
These guys have an API that will give the results. It's also free to use.
Note: they also provide data source download in xls or sql format at a premium price. but these data also provides technical specifications for all the make model and trim options.
Punith Raj1,50333 gold badges1919 silver badges3838 bronze badges
ChrisChris
How about Freebase? I think they have an API available, too.
ArwymArwym
Not the answer you're looking for? Browse other questions tagged database or ask your own question.
![Car Database Example Car Database Example](http://www.oref.co.uk/wp-content/uploads/2018/04/Feb-2018-EV-report-crop.png)
MAKE - MODEL - YEAR - MOTOR are the entities. I'm working with these 4 entities, but I haven't found the best design (I guess is wrong)
I was watching this website to investigate how is organized: http://www.rockauto.com/catalog/raframecatalog.php
I'm still having doubts about the design fot these four tables.
DarfDarf98222 gold badges1212 silver badges3333 bronze badges
4 Answers
So consider what makes up the database
Vehicles / Motor which would contain
- An identifier (registration plate?)
- The manufacturer
- The model
- The year it was made in.
Now, without normalisation that's fine, but you can see there will be duplicated information in there, so you'd extract that one by one. First you'd take out manufacturer into a separate table, so now Motor would become
- An identifier
- The manufacturer ID (a foreign key to the manufacturer table)
- The model
- The year it was made in.
The manufacturer table would hold.
- The manufacturer ID
- The manufacturer name.
Now you can look at model. But a model is really only made by a single manufacturer, so, hmm, we could do this for the model
- The model ID
- The model description
- The manufacturer ID (foreign key to the manufacturer table)
And motor would become
Motor would become
- An identifier
- The model ID (foreign key to the manufacturer table)
- The year it was made in.
Personally, I'm not sure I would normalise the year into a table of its own. The year would be an integer field, and the ID column would probably also be an integer field, so you're not saving much ...
blowdartblowdart47.6k1010 gold badges9191 silver badges138138 bronze badges
![Database Database](http://www.databaseanswers.org/data_models/car_sales/images/data_model.gif)
My view is that you have a vehicle which is a composite of the following aspects
You would then have the components of a vehicle be marked as distinct as a collection so your don't insert a vehicle twice.
Please note that these are incomplete tables and are just to give you the bare bones of an idea. You should probably with manufacturer mark if they are a regional variant so you can adequately distinguish between a Ford Focus for UK sale and one for US sale.
JamieDaintonJamieDainton
Without adding any extra complexity to the RockAuto site it appears to structure it's tree view as make-year-model-variant but 'year' doesn't really have any attributes or children or parents, it's more like an attribute of model.
You might consider having the model with a date range, but then the same would apply to each variant as well. Once you start adding any further complexity you'll be down to VIN numbers .
K. BobK. Bob2,30811 gold badge1414 silver badges1515 bronze badges
actually if this isn't a homework task (which I suspect It is) I would use some orm tool and take the results (or tweak them), actually you could also do that with homework - but i'll guess that would be cheating
anyway I would guess a quite different table mapping, guessing similiar too @jamieDainton that you are missing an entity that ties it all together like vehicle
alonisseralonisser4,9561515 gold badges6363 silver badges109109 bronze badges