moving data between tables with constraints

Question

I have a database with linked tables I use for an item planner program I created. Item table and mission table have a link table (linkmissionitem) to identify which items are used in which missions. There are other tables linked to them as well.

 

If an item is either stolen or broken I hide the item from appearing in the planner list by changing its status field. I do not want to delete the item but I couldn't anyway due to the constraints between the tables in the database. Ideally, I would like to move the items to a table called “Archive”, for example. This way I will still be able to access the item data and still interrogate the database for items used in previous missions. 

  

As old material is replaced by newer I will want to move the older unused items to the archived table too.

 

My question is, is there a way to move the items to the archive table despite the constraints or is it best just to continue to hide the items from view? My concern with hiding is the work the program has to do to display the available items in the date planner and the mission colours thus slowing the program down.

 

I have included the EntityDesignerDiagram and a screen shot of the planner program that I hope will help explain the above more clearly.

 

Many thanks.

 

---

:-( Still trying to program

Answer 1

I would suggest to keep it in the same table and hide (using "IsDelete" column) it thru the program as you are not dealing with millions of recrods. It's up to us how we write a linq query to get the result, also if we have a proper index then we don't have any issue at all.

Answer 2

Thank you tsathiyan, I hadn't thought of doing it that way. How stupid of me.

Many thanks.

---

:-( Still trying to program