DB layout help
I currently have 3 tables requisition, items and location. Requisition has information about when req create, what PO is assigned etc. Location as information about the location of the equipment by tag number building what room, who has it etc. Items have information about inventory of the equipment tag number, PO attached to, device manuf, model, s/n, p/n etc.
The problem I am confused on is weather or not to add WarrantySerialNumberReplaced, where the equipment serial number of the unit replaced by is entered and also WarrantySerialReplacedDate to the items table. I will need to keep the original serial number to the tag so that it could be looked up later for inventory check/lookup with auditors or such things.
How would it affect me searching my tag number or serial number?
Should I create a Warrenty table?
I was thinking off having it marked as removed/disposed inventory and put warranty replaced date and serial boxes like above but then reentering the tag model device etc back into the items field to create a new line item like new equipment. I need to keep the same tag number because once the PO is closed only those items can be attacahed.
Also should I create another table for removed Items instead of keeping them in items with a removed field and details? Then have a script move the equipment information to that table?
Just trying to think of the correct way of creating this DB for ease of use and management.