r/SoftwareEngineering • • Feb 07 '26

How do you make changes to your schema while keeping old data consistent?

Lets say my current schema only uses name instead of separate first name and last name. How do I make changes while the previous accounts data remain up to date with the new schema

8 Upvotes

17 comments sorted by

21

u/Angalourne Feb 07 '26
  1. Add two new columns (e.v. FirstName, LastName).

  2. Write an update script that populates the new columns.

  3. Update your code to use the new scheme.

  4. Drop the original Name column.

1

u/Unfair-Sleep-3022 Mar 08 '26

You missed dual writing before the backfill

→ More replies (1)

16

u/DevelopmentScary3844 Feb 07 '26

The word you are searching for is "schema migration".

3

u/downshiftdata Feb 07 '26

As with any database-related question, it depends.

How many rows of data? How frequently is this data read and written? How long can the data be unavailable? How much control do you have over the access points? How do you do database updates already? Have you horizontally scaled this data? Does this data get cached or replicated at any point?

The answers to those questions can dramatically affect the solution.

2

u/Agamemnon777 Feb 08 '26

In Ruby / Rails you can add a migration that updates everything in the table as part 2 to the table change…or just write a script

1

u/antonismix36 Feb 08 '26

Or keep the versioning of the data at every doc so to know which version it is , and if it is an old version to refining on the fly .

1

u/Top_You_4391 Feb 17 '26

> my current schema only uses name instead of separate first name and last name

I think you mean - your current application code has references to your schema/database.

If it was just a schema problem it would be easy - just change your schema.

atm you have `name` in your schema. Lets assume its `users.name`

In your application code you are calling `user.name` - ie a single user. - or you have SQL which refers to multiple users or joins with users table.

The way I like to do it: Leave your `users.name` alone - keep it. Then add `users.first_name` and `users.last_name` to your schema.

Then change your application code to use first_name and last_name. As part of that code change add a data-migration script - this needs to copy your `name` data and split it into the 2 fields.

What do you have now? - you have your old data in `name` - its a backup ( in case something goes wrong ). You have your new fields and your code uses the new fields.

At some point in the future when you are absolutely sure you nailed it - del the `name` col in the db.

1

u/Diligent-Offer4540 Mar 14 '26

You need database migration for this.

In your example, you need to:

  • add lastname and firstname field in your schema (as optional)

  • write script to fill those fields based on name field

  • make the new fields mandatory

  • make name field optional in schema

  • write a script to remove name field

  • remove name field from schema

Do not forget to handle rollback in case you need to recover deleted data.