Save value from last year in current table

This is a more general question about database designing. I am currently migrating an Excel/VBA program to SQL and got following situation where i need food for thought, as i am doing this the first time.

Current situation in excel: excel sheet

Part of the new MySQL table so far:

+--------------+-------------------+---------+----------+-------+---------------+
| licenseplate | mileage_last_year | january | february | march | first_quarter |
+--------------+-------------------+---------+----------+-------+---------------+
| BP 11-111    |              NULL |     100 |      100 |   100 |           300 |
| BP 11-222    |              NULL |     561 |     1111 |   707 |          2379 |
+--------------+-------------------+---------+----------+-------+---------------+

“first_quarter” and so on are generated columns, no problem so far.

But what is the best practice to save the mileage from the current year for the next year?

1. A new table for every year and query it from the frontend, depending of the users selection?
2. A new row for every licenseplate/car and year and only display the current year in the frontend?

Answer

First you need to decide what is the granularity of the data you are going to store: here, it is at year, month level milage. So, accordingly you can decide the table structures and granularities.

You need to normalize into different table.

• Year Table(Year)

• Month Table(MonthId,MonthName,QuarterName)

• LicensePlate Table(LicensePlateId,LicensePlateName)

• Mileage Table(LicensePlateId, Year, MonthId, Milage)

You can now derive, what was the mileage for a specific year and month for a license plate. You can aggregate values at Quarter level when needed.

Note: You can combine Year, Month information into single calendarMonthTable(CalendarId, Year, Month, MonthName, QuarterName) which is at granularity of month level also.