DB design - keeping track of revision history

Databases
1

I’m building a database where a portion of it keeps track of revision history of stored data. However, I’m at a sticking point. The example below has been watered down to show the problem area.

Here’s the diagram:

Here’s the SQL:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`entry`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`entry` (
  `entry_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `history_id` BIGINT UNSIGNED NOT NULL ,
  PRIMARY KEY (`entry_id`) ,
  INDEX `fk_entry_entry_data` (`history_id` ASC) ,
  CONSTRAINT `fk_entry_entry_data`
    FOREIGN KEY (`history_id` )
    REFERENCES `mydb`.`entry_data` (`history_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`entry_data`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`entry_data` (
  `history_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `entry_id` BIGINT UNSIGNED NOT NULL ,
  `data1` VARCHAR(45) NULL ,
  `data2` VARCHAR(45) NULL ,
  `data3` VARCHAR(45) NULL ,
  PRIMARY KEY (`history_id`) ,
  INDEX `fk_entry_data_entry1` (`entry_id` ASC) ,
  CONSTRAINT `fk_entry_data_entry1`
    FOREIGN KEY (`entry_id` )
    REFERENCES `mydb`.`entry` (`entry_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`log`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`log` (
  `log_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `longIP` VARCHAR(64) NULL ,
  `os` VARCHAR(45) NULL ,
  `browser_agent` VARCHAR(255) NULL ,
  PRIMARY KEY (`log_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`entry_logs`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`entry_logs` (
  `entry_id` BIGINT UNSIGNED NOT NULL ,
  `log_id` BIGINT UNSIGNED NOT NULL ,
  PRIMARY KEY (`entry_id`, `log_id`) ,
  INDEX `fk_entry_logs_log1` (`log_id` ASC) ,
  CONSTRAINT `fk_entry_logs_entry1`
    FOREIGN KEY (`entry_id` )
    REFERENCES `mydb`.`entry` (`entry_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_entry_logs_log1`
    FOREIGN KEY (`log_id` )
    REFERENCES `mydb`.`log` (`log_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Basically, in the “entry” table, both IDs should be primary keys, in order to have multiple “history_id”'s for each “entry_id”. However, when I do that, then I’ll have to reference both columns in the “entry_logs” table, when all I need for that is the “entry_id”.

How would I get around needing to reference “entry_id” and “history_id” when all I really need from other tables is just the “entry_id”?

2

I made the example fairly generic.

Ok, so there’s the “entry_data” table, which has all the data for a particular record. Normally, that would’ve contained the “entry_id” PK and would’ve been a table all by itself. However, I wanted to keep a history of revisions of the “entry_data” table. So if a user wanted to see who changed what, or wanted to revert back to an older revision of the data, they could.

3

Does that help?

entry_id is an auto-incrementing PK of entry
history_id is an auto-incrementing PK of entry_data

The other two tables are there just for reference to show how I’d like to use the “entry_id” as a FK in other tables.

Now that I think of it, would it be more appropriate to go with a 3-table layout like this instead?

4

Is it the best approach, though? To me, it seems a bit silly to have a table with only one column in it.

5

could you please explain your keys on each table? both primary and foreign

i just can’t wrap my head around the entry and entry_data tables referencing each other with foreign keys

not without help

:slight_smile:

6

i guess that depends

you never did explain what any of that stuff means :wink:

what’s a history? what’s an entry? why ~doesn’t~ an entry have any data attributes?

7

that makes ~way~ more sense to me

:slight_smile: