Home / Questions / 379991
Accepted
Grem
Asked: 2020-08-06 18:28:44 +0800 CST 2020-08-06 18:28:44 +0800 CST

Extend the columns of a database with records

  • 772

Taking into account that things change over time, one of the biggest questions running through my head at the moment is: what should I do if I want to change my business logic to store more information about a specific entity, since this must be persisted and subsequent records already exist: What happens to existing records?

Should all new fields be nullable so previous records don't break the database?

And something more controversial: what if I need to delete one of the columns? Should I go directly to the database, before deploying the new features, and execute a query that deletes all those records so as not to cause problems?

base-de-datos

1 Answers

  1. Best Answer

    It is common for systems that are already in production to suffer from updates, both due to improvements/evolution of the software itself, and due to changes in the operation of companies/industries and the legal framework that regulates them.

    With this, database structures evolve as well. The way to update/evolve a database structure varies depending on the database engine and the tools at your disposal.

    If we talk about relational databases, beyond the mechanisms used, in the end, it will be DDL statements that change the structures.

    When creating new columns, several strategies can be taken:

    • Create the columns nullable, so that there is no conflict with records that already exist in the database.
    • Create new columns with a default value and the attribute not nullset.
    • Create new columns like nullable, then perform some statement to derive the value they should have, and then change the definition of the column like not null.

    As part of the evolution, it is also common for new tables to be created, columns that are already obsolete in the new model to be dropped, and tables to be dropped for the same reason.

    Here you will also see cases where, after creating a new table, information is flipped from an existing table to fill it and then the existing table is deleted. This is common when the structure has undergone re-engineering processes.

    Depending on the capabilities of the database engine, these operations could be carried out in the context of a transaction, so if something fails during the evolution of the structures, you apply a rollbackand leave everything as if nothing had happened.

    If the engine does not allow it, the only way to be safe is to make a copy or backup and if something fails, restore from the copy.

    • 2