How to create data tree in MS Access

Question

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.

Answer 1

I believe you are asking about a table design or schema.

Whether you use 1 or many tables is up to you. The increased complexity of normalisation is a trade off between efficiency and convenience.

The easiest option to begin with is to use a single table to describe the car, and create a field for each piece of information you want to store. Start with number plate, make, model, year...

If you want to limit or restrict the data that can be entered you can add reference tables with foreign key constraints.

If your model is going to be be detailed enough to describe things like engine models, individual parts or a detailed service history then you will probably want to use seperate tables.

The key question here when thinking about the data is: "When i think about one car, does it have one or many of these things?" That will give you a good indication if you will need to add a table for that 'thing'

Sometimes we dont have all the information we would like to have. Thats just a fact of life. You can either collect the missing info, or design your table(s) so that the data is not mandatory.

You need to decide if the lack of detail is going to be a problem for you. Can you achieve want you want with the data you have? Will more data improve the accuracy or output of your database?