0

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.

  • 1
    "but i was then told, they would like to keep the old ID number." That's a fundamentally ridiculous requirement. It's a different asset, and should have a different number. Conceptually, if the number is supposed to remain the same, then the number identifies something other than the asset. It sounds as if that other "something" needs its own table, referencing the actual physical asset, whose ID might not actually be visible. – Michael - sqlbot Apr 28 '18 at 2:50
  • i agree with you and i am still going to try and push my point and have it done my way, but at the end of the day, they employ me, not i them so if they say that the ID has to stay the same then i need a strategy ready. i really like the idea of marking the old record as obsolete (thinking of having a "state" field for deleted, replaced, decommissioned, so on) and then a field called replaces (or something) that is filled in on the new asset, that way you see the new asset and if need be can trace it back, then back another level if it gets replaced again. – mike16889 Apr 28 '18 at 14:18
  • Sounds like you need two ids -- one to keep the database happy, and one to keep your employer happy. Note that there is a many:1 relationship between them. – Rick James May 15 '18 at 19:15
0

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.

  • that is the initial way i was going to do it until my boss told me that he wanted the ID to stay the same. – mike16889 Apr 27 '18 at 2:59
  • Put a field something like previous_id, copy your existing record to new raw with previous_id as primary_key of existing record. Update existing record with new data. Dont know if Iam confusing you with my English. And you can determine the data in the order at which it is present in the table. – Sanal K Apr 27 '18 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.