Are updates the only disadvantage of denormalization?

Question

I am trying to understand what are the disadvantages of denormalization. Say that I have the following database (School_has_Student is a denormalized table):

I have read that the problems that you may face when using denormalization are related to the operations of INSERT, UPDATE, and DELETE.

Now I understand why we have the UPDATE problem, because if I updated one piece of data, then I would have to update the rest of the related pieces of data (for example: if I updated the student name "Paul" in the Student table, then I would have to also update the student name "Paul" that exist two times in the School_has_Student table), but I don't understand why INSERT and DELETE are also problems.

Note: I know that the increase of storage space is also a disadvantage, but I do not think it is a big disadvantage with current storage devices which have very large capacity.

Answer 1

There are different kinds of denormalization. Say we can have the main table readouts like that:

CREATE TABLE `readouts` (
  `sensorID` INT(10) UNSIGNED NOT NULL,
  `timestamp` TIMESTAMP NOT NULL,
  `value` FLOAT(9,5) NOT NULL,
  PRIMARY KEY (`sensorID`, `timestamp`)
);
+----------+-----------+-------+
| sensorID | timestamp | value |
+----------+-----------+-------+

Table stores the readouts from the lot of sensors. If we need the last readout for each sensor we have to run the query like that:

SELECT a.*
  FROM readouts AS a
  JOIN ( SELECT sensorID
              , MAX(timestamp) AS timestamp
          GROUP BY sensorID
       ) AS b  ON b.timestamp = a.timestamp
              AND b.sensorID  = a.sensorID
 ORDER BY sensorID ASC
;

That is quite heavy query even when the table readouts is properly indexed. We can denormalize the database by creating the table of the same structure but the UNIQUE constraint for sensorID:

CREATE TABLE `last_readouts` (
  `sensorID` INT(10) UNSIGNED NOT NULL,
  `timestamp` TIMESTAMP NOT NULL,
  `value` FLOAT(9,5) NOT NULL,
  PRIMARY KEY (`sensorID`)
);

Now we have to INSERT incoming readouts to the both tables. The second table should be inserted in the special way:

INSERT INTO last_readouts 
     VALUES (sID, ts, val) 
     ON DUPLICATE KEY UPDATE 
        `timestamp` = ts
         value = val
;

This addtitional INSERT is the insert-related disadvantage of the denormalization. But the advantage is the minimal cost of the query

SELECT * FROM last_readouts;

The DELETE disadvantage is of the same sort - you need more operations of DELETE but you have some benefits instead.

Answer 2

There are more ways to denormalize a model than your example, but in your case, an example of an INSERT anomaly would be:

INSERT INTO SCOOL_HAS_STUDENT 
    (school_id, school_name, student_id, student_name)
VALUES
    (2, 'A third school', 1, 'Tim');

I.e.

SELECT distinct school_name 
FROM SCOOL_HAS_STUDENT 
WHERE school_id = 2

contains invalid information

I also agree with you that storage is not the major concern, it is that we can derive false information from the model. If we stretch things a bit, the model and the data is our axioms. The result of our queries are theorems that can be deduced from the axioms. With incorrect axioms, anything can be deduced.

Answer 3

Delete suffers from the same problem as Update - repetition of work.

Doing anything once takes a finite amount of time.

Doing the same thing a hundred times takes at least a hundred times as long.

Remember that every single change in a database incurs writes to disk, even if it's only writing to a Transaction log, instead of the data file(s) proper. Yes, disk space is cheap but, compared to querying data blocks that are sitting, in memory, in the database's Buffer Cache, writing .. to ... disk .... is ..... slow ......
You don't want to be doing any more of it than you really have to.

Also, you have to know where the duplicated data is - if someone [else] adds another table with more duplicate copies of the data, then all your insert, update and delete logic have to be told about it - they have no way of working it out for themselves.