I have a database for a daily sales report, so I can see how many product I sold on a specific day. For example:

16/01/2021 -> 50 apples were sold  

16/01/2021 -> 30 lemons were sold  

17/01/2021 -> 46 apples were sold  

17/01/2021 -> 34 lemons were sold

I have these tables:

`products`

* id INT AUTO\_INCREMENT PRIMARY KEY
* name VARCHAR(100) NOT NULL
* price FLOAT NOT NULL
* Other stuff

`sales`

* saledate DATE,
* product\_id INT,
* quantity INT,
* PRIMARY KEY (saledate, product\_id),
* FOREIGN KEY (product\_id) REFERENCES products(id),

It looks to works correctly. Now I have to give the option to delete a product. Obviously the "history" of the product has to remain. For example, if I delete "apple" from product table, I still need to know that "16/01/2021 -> 50 apples were sold".

I know there are two ways to do that: soft delete or use an archive table. With soft delete I can add a flag "is\_deleted" to my "products" table, but in this way:

1. The "products" table will be heavy and slow with the passing years beacuse there could be many many products in the table
2. I will need to add the WHERE clause in my queries

So I thought to use an archive table, where i will move the product from the "products" table to the archive table (from now I will call it "archived\_products" table). Here are the problems

1. **If I move a product from "products" table to "archived\_products" table, the FOREIGN KEY in the "sales" table will be still linked to the "products" table**.
2. **Is it possible to restore the product from "archived\_products" table to "products"? (obivisuly it need to keep it's old id on "products" table**

If you need, I can to provide an example for the 1. point

Thanks in advance and sorry for my bad english