1

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):

enter image description here

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.

  • You must look not only direct, but backward too, it seems. In backward case (as I understand) denormalization are related means that if your data is already unconsistent the result of operations will differ dependent by the field which will be used to identify a record, which referent will be altered. – Akina Oct 19 '18 at 11:32
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.