Ensure same tenant in a Many to Many relationship

Question

I am trying to draft a very simple data model for a multi-tenant database.

So far I have only 3 tables:

• company: pretty much my tenant
• account: is part of one company and zero or more groups
• account_group: same as account: one company and zero or more accounts

Here is the SQL:

CREATE TABLE company (
  name TEXT NOT NULL,

  PRIMARY KEY (name)
);

CREATE TABLE account (
  email_address TEXT NOT NULL,
  company_name TEXT NOT NULL,

  PRIMARY KEY (email_address),
  FOREIGN KEY (company_name) REFERENCES company (name)
);

CREATE TABLE account_group (
  name TEXT NOT NULL,
  company_name TEXT NOT NULL,

  PRIMARY KEY (name, company_name),
  FOREIGN KEY (company_name) REFERENCES company (name)
);

CREATE TABLE account_group_membership (
  account_group_name TEXT NOT NULL,
  account_group_company_name TEXT NOT NULL,
  account_email_address TEXT NOT NULL,

  PRIMARY KEY (account_group_name, account_group_company_name, account_email_address),
  FOREIGN KEY (account_group_name, account_group_company_name) REFERENCES account_group (name, company_name),
  FOREIGN KEY (account_email_address) REFERENCES account (email_address)
);

In this specific case, there is no constraint between the Account Company and Group Company.

After doing some search, I found this question and tried to add the following to my account_group_membership table;

CREATE TABLE account_group_membership (
  account_group_name TEXT NOT NULL,
  account_group_company_name TEXT NOT NULL,
  account_email_address TEXT NOT NULL,
  account_company_name TEXT NOT NULL,

  PRIMARY KEY (account_group_name, account_group_company_name, account_email_address),
  FOREIGN KEY (account_group_name, account_group_company_name) REFERENCES account_group (name, company_name),
  FOREIGN KEY (account_email_address, account_company_name) REFERENCES account (email_address, company_name),
  CONSTRAINT same_company CHECK (account_group_company_name == account_company_name)
);

Unfortunately this doesn't work since the company_name column of the account table is not unique.

I cannot:

1. Make the column company_name unique since multiple accounts can be from the company.
2. Use email_address and company_name as primary key for an account. The email_address should be unique in the whole table. Also account will mostly be queried by email_address and from time to time by company_name.
3. See any other solution actually....

So is there any simple way to do this (simple as in easy to understand and maintain) ? Alternatively, does having this problem, mean that there is something wrong going on in the design ?

Answer 1

This is almost an anti pattern if you ask me.

• All accounts have a company
• All account_groups have a company
• They're all linked through a linking table which also has a company.

So, everything has a company: except when it doesn't? And if a company has a subsidiary you're in a world of trouble. It may make sense. But, what you seem to be doing is creating buckets which you can relate to in order to emulate hierarchy; however, the hierarchy is easier to just have in all it's glory.

CREATE TABLE accounts (
  accountid             serial PRIMARY KEY,
  name                  text   NOT NULL,
  email                 text,
  contact_name_given    text,
  contact_name_surname  text,
  parent_accountid      int    REFERENCES accounts
);

INSERT INTO accounts (accountid,name,email,contact_name_given,contact_name_surname,parent_accountid) VALUES
  (1,'ACME', '[email protected]',null,null,null),
  (2,'ACME Sales', '[email protected]', 'John D', 'Supervisor', 1),
  (3,'Evan Carroll', '[email protected]', 'Evan', 'Carroll', 2);

Then get the list with a RECURSIVE CTE

WITH RECURSIVE t(accountid, name, email, contact_name_given, contact_name_surname, level)
AS (
  SELECT
    accountid,
    name,
    email,
    contact_name_given,
    contact_name_surname,
    0
  FROM accounts
  WHERE parent_accountid IS NULL
  UNION ALL
    SELECT
      a2.accountid,
      a2.name,
      a2.email,
      a2.contact_name_given,
      a2.contact_name_surname,
      t.level+1
    FROM t
    JOIN accounts AS a2
      ON ( t.accountid = a2.parent_accountid )
)
SELECT *
FROM t;


 accountid |     name     |          email          | contact_name_given | contact_name_surname | level 
-----------+--------------+-------------------------+--------------------+----------------------+-------
         1 | ACME         | [email protected]        |                    |                      |     0
         2 | ACME Sales   | [email protected]          | John D             | Supervisor           |     1
         3 | Evan Carroll | [email protected] | Evan               | Carroll              |     2
(3 rows)

Now you just need to give logical names to the level = 0,1,2 if that's something the task really requires. or make the schema more accommodating of different types of entities in the hierarchy.