如何为多租户SaaS产品设计有效的数据库？

Question

My team and I are building a SaaS product which is to be used by multiple businesses across the world. To put it simply, our software does some basic accounting for them and alerts them if some document is due for renewal.

The problem is, there may be just 3 such documents for each such company in India but 4 in Sri Lanka and 7 in Singapore. And each field may be named differently. For example, the three fields may be called "Drivers' License", "Insurance", "Permit" in India but they may be called "XYZ","XXYZ" and "XZY" in Singapore.

What is the best and most effective way to implement such features using MySQL. Let's say I can cap the number of such columns at 5. Also, I do not want to use NoSQL for this project.

We're using PHP running on CodeIgniter and we plan to deploy on Engineyard.

Answer 1

CREATE TABLE Countries (
  CountryCode CHAR(2) PRIMARY KEY,
  CountryName VARCHAR(255),
);

CREATE TABLE Companies (
  CompanyID   SERIAL,
  CountryCode CHAR(2) REFERENCES Countries (CountryCode),
  -- etc.
);

CREATE TABLE Documents (
  CountryCode  CHAR(2) REFERENCES Countries (CountryCode),
  DocumentID   INT UNSIGNED,
  DocumentName VARCHAR(255),
  -- etc.
  PRIMARY KEY (CountryCode, DocumentID)
);

CREATE TABLE CompanyDocuments (
  CompanyID   BIGINT UNSIGNED REFERENCES Companies (DocumentID),
  CountryCode CHAR(2)         REFERENCES Countries (CountryCode),
  DocumentID  INT    UNSIGNED,
  Expiry      DATE,
  PRIMARY KEY (CompanyID, DocumentID),
  FOREIGN KEY (CountryCode, DocumentID) REFERENCES
    Documents (CountryCode, DocumentID)
);