We are storing assets in an asset table, using auto Increment to assign an ID to an asset.
I have been asked what happens when an asset is replaced eg, water heater leaked, replaced with a new one.
I suggested that in the interface a button to replace the item, would create a new row, with some of the details copying over (like location) and on the old record having a replacedBy column that just lists the new ID. then when querying for all in service assets, use WHERE replacedBy IS NULL
but i was then told, they would like to keep the old ID number. i thought for a second of coping the record in entirety and "inverting" the id, eg 55 would become -55, essentially archiving it, then making changes on the original record. but then what happens if the new one is then replaced.
i could just copy the record to the next auto increment value and having a field to say it is replaced by the original but then i have an old record with a higher id than a newer record, something inside me just does not sit well with that (probably some ocd thing)
is there a way to auto decrement from 0, eg replaced items would be copied to the next negative value, so everything under 0 would be old data and everything over 0 would be current stuff?
there are a lot of asset tables so i would like to avoid having a table for each asset just to store replaced assets.
Best Answer
You could add one more field in your table, something like parent_id where you could store the primary key from which the new record is derived. And primary key will continue to auto-increment. I hope this will fix your confusion.