How can i explain my coworker that this is unnecessary complexity and redundant data?

He wants to have a table of years because many tables have the 'year' value, and he also want to have relation table with names and years, this would add unnecessary inner joins and Foreign i insist that this is wrong, i just want to make sure this is not a good practice...

Database diagram picture

  • Perhaps a calendar table is a concept worth looking into. – LowlyDBA Feb 6 at 17:17
  • @LowlyDBA - A vehicle "model year" is probably independent of "time zones". OTOH, reporting on how many sales happened in a "day" might worry about the timezone for the "date" in a report. – Rick James Feb 10 at 19:17

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.

| improve this answer | |
  • Just to add, OP can end with a range of years in the [VehicleModel] table because users can need to select a (valid) year for a specific model search. – jean Feb 10 at 19:06
  • @jean - Good point. Meanwhile, I would lean toward model being the spelled out, not turned into an ID. (Hmmm... Can two different manufacturers have the same "model"?) – Rick James Feb 10 at 19:14
  • It can become extremely complex if we start to think about all small things like the same model can be fabricated in a number of plants in a number of countries in different periods, it can need even a new [BaseVehicleModel] table. But all is speculation over real OP requirements. Most of the times a [Observation] field will be sufficient – jean Feb 10 at 19:29

I used a years table for reporting where it was possible with the right amount of filters not to have any results for that year. Client still wanted to see the amount for that year even if it was zero. By having a years table you guaranteed that there would a value of for every one. The alternative was doing outer joins which I felt would impair performance by requiring more scans.

Your use case may vary but it's not always a bad idea

| improve this answer | |
  • Interesting. May we says that a years table could be useful in cases where analytics over dates are a primary requirement required? I wonder what would have been done if they wanted months too, though. – watery Feb 6 at 17:12
  • @watery I had a months table too for the same solution. It also helped when the fiscal year was not the calendar year. So cal_years, mon_years, fiscal_years, fiscal_months – kevinsky Feb 6 at 19:07

As a quick answer, I'd say you're using a number to relate another number - and only that (no extra value in present in that years table).

Then, as you already said, querying for years would require extra efforts (joins, subqueries, and so forth).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.