I might suggest to reorganize schema, to make it more scalable.
DROP
columns entityId
and entityType
.
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.
- 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;
Best Answer
I don't think there is
many-to-many
relationship exists in this case as theIntern
will always be associated with a single company.I would prefer to go with say a
LOG
(orInternCompanyHistory
) table to saveInternId
,CompanyId
andDateOfChange
or any other attributes associated with change of company.