Mysql – How to isolate client data on the same application using MySQL

One question I have is how does the Company relate to a phone number? I mean, is there just a list of internal numbers, and will all of these phone numbers be associated to an employee. If they are you can simply just make your phone number table and have it relate to an employee. For example.

create table company
(
company_id int PRIMARY KEY IDENTITY, --(Primary Key)
company_name varchar(100)
)

create table employee
(
employee_id int PRIMARY KEY IDENTITY,  --(Primary Key)
employee_name varchar(100),
company_id int not null  --(Foreign Key)
)

create table phone
(
phone_id int PRIMARY KEY IDENTITY,
phone_number varchar(15), -- 555-555-5555
phone_type varchar(10),   -- Home | Cell | Work
employee_id int not null  --(Foreign Key)
)

employee_id being your foreign key to your employees table. If each employee only works for one company, then to get all of the phone numbers available for that company, you could do something like this

select * from phone as p join employee as e on e.employee_id = p.employee_id where e.company_id = 1

I hope this helps :)

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;