I am trying to build a database in which I am going to need to have a tree to keep track of cars.
Unfortunately, I don't always know what level of detail I am going to have, and the level that I do have will vary. So although I will presumably know the manufacturer, I may not always know the model, submodel or year. In fact, the further I go down the tree, the less likely I am to know.
However, my best data would be the most complete, so I don't want to just throw that away. I would like to have a place in my DB where data on the cars is stored that contains all this, but while I might see a 2017 Nissan Maxima SL, most of the time I might only be able to make entries into my DB about a Nissan Maxima (no year or submodel).
It's also really important that I minimize the number of fields in the main table. How would I structure this? Is there a way to set it up so I don't have to have separate fields for year, make, model and submodel?
If I do, and all those fields exist in my models table, would I have to bring them all into my main table if that's where most of my records are being stored?
Keep in mind I will be making multiple entries of 2017 Nissan Maxima SLs (for example) in my main table.