CODEWITHSUNDEEP
mysqldatabasescd
exussum
exussum

Reputation: 18568

MySQL Slowly changing dimensions SCD2 + ON DUPLICATE KEY UPDATE

Im looking to have MySQL handle Slowly changing dimensions The table is set up to use 'effective date' as the control - so table structure is similar to here http://en.wikipedia.org/wiki/Slowly_changing_dimension Type II bottom example.

Is it possible to use this function to update / insert or is it best to seperate the functions eg insert = check for existing then insert, update = update original column, insert new column

Thanks

Upvotes: 0

Views: 3050

Answers (2)

Alex Krash
Alex Krash

Reputation: 1

I have an implementation for such approach without any selects (but with insert/update). MySQL: 5.7 Here is table structure:

CREATE TABLE UserStatusLog (
  `user_id` int(11) NOT NULL,
  `status_type` tinyint(8) NOT NULL, // we are logging multiple statuses in one table
  `status_value` int(11) NOT NULL, // actual value of the status
  `is_last` tinyint(1) DEFAULT '1', // marking flag
  `start_ts` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01', // when this status_value became actual
  `finish_ts` timestamp NOT NULL DEFAULT '2030-01-01 00:00:00', // end of actuality for this status
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, // just for logging
  `updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, // just for logging
  PRIMARY KEY (`user_id`,`status_type`,`finish_ts`),
  UNIQUE KEY `uniq` (`user_id`,`status_type`,`is_last`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

We are trying to insert new value to log:

INSERT IGNORE INTO UserStatusLog
(user_id, status_type, status_value, is_last, start_ts, finish_ts)
VALUES ($user_id, $type, $value, NULL, '2017-12-08 15:45:34', '2030-01-01 00:00:00')
ON DUPLICATE KEY UPDATE is_last = IF(start_ts < '2017-12-08 15:45:34' && status_value != VALUES(status_value), 
2 /* THIS IS A "SPECIAL VALUE" for is_last */
, is_last);

Then, we are comparing result of query via mysql_affected_rows();

  • 0 there is a record with start_ts greater than new one, oe

  • 1 this is a first user's record in log, need to perform update, marking is_last for this record:

    UPDATE UserStatusLog SET is_last = 1 WHERE user_id = $user_id AND status_type = $type AND finish_ts = '2030-01-01 00:00:00' LIMIT 1;

  • 2 there was a record, that is elder than new start_ts, and we've updated it with is_last = 2 (SPECIAL VALUE):

    UPDATE test.mock_20171208_47d9ac21808ee65d605ca32205888648 SET is_last = NULL, finish_ts = '2017-12-08 15:45:45' WHERE user_id = $user_id AND status_type = $type AND is_last = 2 LIMIT 1; // insert new, really last record INSERT INTO test.mock_20171208_47d9ac21808ee65d605ca32205888648 (user_id, status_type, status_value, is_last, start_ts, finish_ts) VALUES ($user_id, $type, $value, $is_last = 1, '2017-12-08 15:45:45', '2030-01-01 00:00:00');

Upvotes: 0

fancyPants
fancyPants

Reputation: 51928

No, it's not possible. This statement would then update the row if it already exists, so you would end up with only one record and loose historical info. You need to update the old record first (set end_date to current date) and then insert a new record (with end_date being NULL).

Upvotes: 1

Related Questions