Changes to the database schema are called migrations, what are data changes called

migrationterminology

Not sure where to ask this but here it is anyway.

I understand that changes to the database schema are called migrations from a certain version to another.

What do DBAs call changes to the underlying data, ie. the sequence of INSERT, UPDATE, DELETE? Think of the MySQL binlog for instance. Is there a specific term for this?

Best Answer

Check out DML - Data Manipulation Language. This includes CRUD operations (Create (i.e. INSERT - not to be confused with the CREATE TABLE statement below), Read (i.e. SELECT), Update, Delete) on Tables. It's what people associate most classically with SQL.

Compare this with DDL - Data Definition Language. DDL involves the CREATE TABLE and ALTER TABLE operations (among others - related to INDEXes amongst other things).

For completeness, there is also DCL - Data Control Language. DCL is for the control of access to tables - GRANT and REVOKE (i.e. GRANT READ ON schema_name.table_name TO user_name.

The SQL standard is an attempt to homogenise DML among various vendors - its success has been limited so far. Many servers implement non-standard extensions, their own Stored Procedure language, DDL and DCL. Take a look at the "Procedural extensions" section on the SQL page above.

Changes to a schema (from the same version of the same server to another or even within the same instance) are simply called DDL - CREATE TABLE, ALTER TABLE, DROP TABLE.

Changes to a schema brought about (normally) by an upgrade (e.g. Oracle 9 to Oracle 11) are called migrations - going the other way is called either downgrading or backward migration.

Changes to a schema due to a change of server (i.e. Oracle to SQL Server) are called ports ("porting"). These are normally quite difficult and involved due to the differences between servers mentioned above.

Related Solutions

What do you call something that’s not the primary key

User terms surrogate key and natural key for primary key as a variant.

Surrogate Key:

Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table. Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).

Natural Key:

Keys are natural if the attribute it represents is used for identification independently of the database schema. What this basically means is that the keys are natural if people use them example: Invoice-Numbers, Tax-Ids, SSN etc.

Surrogate Keys vs Natural Keys for Primary Key

PostgreSQL – Best Practices for Schema Changes and Data Migrations Without Downtime

You almost have your answer already:

Create the new structure in parallel
Start writing to both structures
Migrate old data to the new structure
Only write and read new structure
Delete old columns

As for step 3, use something like this (in one transaction):

Insert what is not there yet:

INSERT INTO new_tbl (old_id, data)
SELECT old_id, data
FROM   old_tbl
WHERE  NOT EXISTS (SELECT * FROM new_tbl WHERE new_tbl.old_id = old_tbl.old_id);

Update what has changed in the meantime:

UPDATE new_tbl
SET    data  = old.data
USING  old_tbl
WHERE  new_tbl.old_id = old_tbl.old_id
AND    new_tbl.data IS DISTINCT FROM old_tbl.data;

New data will not be touched, because it is identical in both places.

Best Answer

Related Solutions

What do you call something that’s not the primary key

PostgreSQL – Best Practices for Schema Changes and Data Migrations Without Downtime

Related Question