Best way to have archive which you can access

Question

I have a database that need to be archived one every year. The data from last year will some times needed to be used in the next year so i have a column year that can have 3 values (0 - from current year, 1-from last year to be used, 2-to saved but not used). The DB is as follows :

    Table1
     id,year ...
    Table2
     id,id_table1 ...
    Table3
     id,id_table2 ...
    Table4
     id,id_table3 ...

These are the tables that need to be archived where table1.year = 2 . The archived data needs to be accessible as well and needs to go back at least 2 years. The number of rows per year is around: Table1 - 50 rows; Table2 - 250 (Table1 x5); Table 3 - 2500 (Table2 x 10); Table 4 - 5000 (Table3 x 2);

I looked at partitioning but couldn't figure out how to group the four so that they could all move to a separate partition.

Answer 1

Don't bother with PARTITIONing unless you have over a million rows.

A table with 8000 rows is 'tiny'; keep them all in a single table. If there is already a DATE or DATETIME column, you don't even need an extra column to indicate the "year". And you don't need id_table2 (etc).

If you want to discuss further, please provide SHOW CREATE TABLE and some of the queries.