A
vehicle_year should be datatype YEAR.
Dates should be datatype DATE. Even if you need to break apart a date into its component parts, it is almost always better to do it that way instead of having a dimension table with dates and their parts.
In general, do not "normalize" any 'continuous' value -- dates, integers, floats, etc.
As kevensky pointed out, there is a use case for going the other direction for "reports" that need to display zero for missing years (or whatever). But this is not linked in any way to the main tables. Instead it is used something like
SELECT y.year,
COALESCE(SUM(m.stuff), 0),
...
FROM Years AS y
LEFT JOIN my_table AS m
GROUP BY...
Note how the LEFT JOIN includes all the years from the Years table. (You may want to limit the range with a WHERE clause.)
And the COALESCE is used to turn NULL for a missing year into 0. Or N/A. Or No data. Or whatever.
While I am at it, I would suggest that normalizing "model" is also "over-normalization". The model name spelled out is perfectly fine in the Vehicle table.
When should you normalize?
Not unique -- People's names.
When the value is likely to change -- Vehicle owner (but this needs a many:many table)
There is a lot of ancillary data -- Company (with address, etc)
To save space -- the name is long and the table is big; not for 2-letter country_code versus 4-byte INT.
Model year is self-identifying, never changes, not big, has no ancillary data.
Vehicle make and model are mostly similar to model year. Ditto for engine size, color, price, etc.
Let me branch off into a hypothecal query: "What years (model_years, that is) did Chevrolet produce their Impala model?"
That could be answered by `SELECT DISTINCT model_year FROM Vehicle WHERE make = ...;". That gets the answer from the available Vehicles in your table.
Or you might have it from a historical web site that lists the answer. Now you need a table with PRIMARY KEY(make, model) and various info on the history of old cars.
That leads to a messier situation -- hierarchical info. Note: GM > Chevrolet > Impala > LT. "Locations" have a similar problem: USA > Georgia > Fulton County > Atlanta > address. Generally, normalization at each level is gross overkill and should be avoided.
because many tables have the 'year' value
Well, the 'textbook' argument for normalization fails miserably here. It says that you should normalize in order to have the value sitting in a single place to make it easy to change. But if that year represents a vehicle's model_year for one table, but your child's birthday for another table and your graduation in another, you certainly don't want to be changing the value.
Think of the normalization table as an "Entity" such as a person, a place, a company, a picture, a web posting, etc. You give the Entity a unique identifier (PRIMARY KEY) so that everyone can refer to it easily. In the table, you have a printable name, a location, a "likes" counter, etc, etc.
