What's better: a temporary column or a temporary table? We have a table in our database with a Reference column. For a long time these reference values only pointed to one system thus several legacy apps assume that if it contains value, they should use it with that system.

We're updating that model to add indication which of the external systems the value refers to.

Unfortunately not all the apps caught up with that change, which is causing problems. So as temporary measure we're planning to store the reference elsewhere.

We have two options:


Add a new column to the table
Add a new table


Once all the apps are up to scratch, we'll copy the values from temp location over to the Reference column and then drop the source.

Question

Which of those two options is less messy and has less negative impact especially when it comes to cleaning it up?

Considerations

The table currently has 70k rows, 40 columns. Only rows inserted/updated by new apps will have that temporary value.
As per my experience, adding new table (Option 2) referencing index column would be you best choice. And will be less messy and easy to use.

Also, going further you may be able to add columns to the newly created table.

Hope this help!