1

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 ?

0

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', 'company@acme.com',null,null,null),
  (2,'ACME Sales', 'sales@acme.com', 'John D', 'Supervisor', 1),
  (3,'Evan Carroll', 'ecarroll.sales@acme.com', '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         | company@acme.com        |                    |                      |     0
         2 | ACME Sales   | sales@acme.com          | John D             | Supervisor           |     1
         3 | Evan Carroll | ecarroll.sales@acme.com | 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.

  • The idea of the groups was to create subsets of a company accounts. I shouldn't have named the company table like that, that's really misleading. It is, in reality, a tenant. There is no notion of subsidiaries or to have groups in groups. The constraint could be enforced in the application but I would really like the have a safety net during migrations, maintenance etc... Having an accounts ending up in the wrong group would be pretty bad. – ITChap Apr 6 '17 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.