Ensure same tenant in a Many to Many relationship

1 vote
1 answer
412 views
database-design foreign-key primary-key constraint
                          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 ?
                        
Asked by ITChap (229 rep)
Apr 6, 2017, 04:44 AM
Last activity: Mar 19, 2025, 06:36 AM
Ensure same tenant in a Many to Many relationship

Related Questions
