Mysql – How to maintain referential integrity across a database where I use versioned records while keeping data in sync

Typically what you'd do in this scenario is, instead of deleting the products, just set a bit (or use an int type field if there could be more than 2 options) to denote that it is "deleted", or inactive.

create table Product
(
    Id int identity(1, 1) not null,
    Name varchar(100) not null,
    Description varchar(1000) null,
    IsActive bit not null default 1
)
go

Then, instead of calling a DELETE on that table for a product you'll no longer support, just do this:

create procedure DeleteProduct
    @ProdId int
as

    update Product
    set IsActive = 0
    where Id = @ProdId

go

That way you don't cascade delete your orders records, or make them completely useless by setting their foreign key references to NULL or DEFAULT.

That is a pretty specific use-case that you brought up in your question, which is why I geared the first part of my answer off of that. But the beauty of Referential Integrity is that this is handled by the RDBMS with direction of the database designer/developer. Typically, you'd choose exactly how you want that foreign key to be handled when the referenced primary key entry is deleted or updated.

You didn't specify an RDBMS in your question, and I can't speak for the specifics of other RDBMSes, but for SQL Server you can choose the following:

• ON DELETE NO ACTION : this will prevent the referenced primary key record from being deleted because it has a foreign key referenced (probably a good safetey net for your specific scenario question)
• ON DELETE CASCADE : this will delete the foreign key referencing record when the primary key referenced record is deleted
• ON DELETE SET NULL : sets the foreign key value to NULL when the referenced primary key record is deleted
• ON DELETE SET DEFAULT : similar to ON DELETE SET NULL, except instead of setting the referencing column to NULL, it'll set it to the DEFAULT value for that field

I might suggest to reorganize schema, to make it more scalable.

1. DROP columns entityId and entityType.
2. CREATE an Objects (or Entities) table which will store public / shared information for all objects / entities. It's primary key will migrate to Companies and to Houses with 1:1 relation. For one object might exist only one company OR only one house. Also, you will have to remove AUTO_INCREMENT from Companies and Houses, because in this case they will no longer generate identifiers themselves.
3. Add foreign key to Contacts table from shared Objects table - contacts might exist for any type of object.

Structure might look like this:

You might obtain entity type by following query:

SELECT
    cnt.*,
    CASE
        WHEN NOT cmp.id IS NULL THEN 1
        WHEN NOT hs.id IS NULL THEN 2
    END as entityType
FROM
    Contacts as cnt
        INNER JOIN Objects as obj ON cnt.objectId = obj.id
        LEFT JOIN Companies as cmp ON cmp.id = obj.id
        LEFT JOIN Houses as hs ON cmp.id = hs.id

Also, note that entityId becomes objectId in this case.

Here is a CREATE-script for benchmarking and test:

CREATE TABLE IF NOT EXISTS `Objects` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `shared_property1` VARCHAR(45) NULL,
  `shared_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Companies` (
  `id` BIGINT NOT NULL,
  `company_name` VARCHAR(300) NULL,
  `private_property1` VARCHAR(45) NULL,
  `private_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `FK_Companies_Objects`
    FOREIGN KEY (`id`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Houses` (
  `id` BIGINT NOT NULL,
  `address` VARCHAR(300) NULL,
  `private_property1` VARCHAR(45) NULL,
  `private_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `FK_Houses_Objects`
    FOREIGN KEY (`id`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Contacts` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `objectId` BIGINT NOT NULL,
  `name` VARCHAR(300) NULL,
  `phone` VARCHAR(300) NULL,
  `email` VARCHAR(300) NULL,

  PRIMARY KEY (`id`),

  INDEX `FK_Contacts_Objects` (`objectId` ASC),

  CONSTRAINT `FK_Contacts_Objects`
    FOREIGN KEY (`objectId`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;