CODEWITHSUNDEEP
phpmysqlcodeignitersaasengineyard
Manikandan
Manikandan

Reputation: 40

How to design effective databases for a multi tenant SaaS product?

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.

Upvotes: 0

Views: 1692

Answers (2)

eggyal
eggyal

Reputation: 126025

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)
);

Upvotes: 0

nvanesch
nvanesch

Reputation: 2600

There are 2 approaches to a multi tennant, single DB system. This site shows some very nice explanation.

http://msdn.microsoft.com/en-us/library/aa479086.aspx

Shared schema or seperate schema.

But besides how to solve the multi-tennant problem, it seems your question is also about "how to store different document types for different companies based on their country" and this question is not closely related to multi-tennancy. It is more general problem in relational DB design. But here is an approach you could use:

Country -- basic country list
 \ id
 \ name

Company -- basic information about company
 \ id
 \ name
 \ countryid

DocumentType -- Diffrent types of documents stored
 \ id
 \ name

DocumentTypeCountry -- this linked allowed (or mandatory, as you want to define it) document types to a country
 \ id
 \ documenttypeid
 \ countryid

Document -- the document itself
 \ id
 \ companyid
 \ documenttypeid
 \ moredocumentfields

Upvotes: 2

Related Questions