Multi-Tenant DB With Many to Many Relationships

[alteredorange]

The Problem:

Hello! I'm new to Citus, so I'm trying to wrap my head around everything and make sure I set up my DB correctly. A slimmed down version is shown below. Basically a user can create multiple companies, and each company will have multiple stores. A user can be an "owner" of multiple companies, while also being invited to manage a company (or certain aspects of it). Each company will have one or more stores. A company can also invite a user to only manage a specific store, so they would be part of that company with access to one or more stores. Every store can have multiple employees and products and products can have multiple reviews.

I think I have the basics down, below is an sql statement for setting up a bare bones DB. Any pointers you can give, or if anything looks fishy, it would be greatly appreciated! My main concerns are:

1. How do you shard users, since they can be in multiple companies(tenants)?
2. How would you shard the user_store table? Would you track company_id in that table as well?
3. Does everything look normal? Don't want to start off on the wrong foot :)

Thanks for any potential help, I really appreciate it!

Basic diagram:

Basic SQL:

CREATE TABLE users (
	user_id uuid DEFAULT uuid_generate_v4() NOT NULL PRIMARY KEY,
	email text NOT NULL,
);

CREATE TABLE companies (
	company_id uuid DEFAULT uuid_generate_v4() NOT NULL PRIMARY KEY,
	created_by uuid NOT NULL REFERENCES users (user_id),
	name text
);

CREATE TABLE stores (
	store_id uuid DEFAULT uuid_generate_v4() NOT NULL,
	company_id uuid NOT NULL REFERENCES companies (company_id),
	name text,
	PRIMARY KEY (company_id, store_id)
);

CREATE TABLE employees (
	employee_id uuid DEFAULT uuid_generate_v4() NOT NULL,
	store_id uuid NOT NULL REFERENCES stores (store_id),
	company_id uuid NOT NULL REFERENCES companies (company_id),
	name text,
	PRIMARY KEY (company_id, employee_id),
	FOREIGN KEY (company_id, store_id) REFERENCES stores (company_id, store_id)
);

CREATE TABLE products (
	product_id uuid DEFAULT uuid_generate_v4() NOT NULL,
	store_id uuid NOT NULL REFERENCES stores (store_id),
	company_id uuid NOT NULL REFERENCES companies (company_id),
	name text NOT NULL,
	price int,
	PRIMARY KEY (company_id, product_id),
	FOREIGN KEY (company_id, store_id) REFERENCES stores (company_id, store_id)
);

CREATE TABLE reviews (
	review_id uuid DEFAULT uuid_generate_v4() NOT NULL,
	product_id uuid NOT NULL REFERENCES products (product_id),
	company_id uuid NOT NULL REFERENCES companies (company_id),
	body text,
	PRIMARY KEY (company_id, review_id),
	FOREIGN KEY (company_id, product_id) REFERENCES products (company_id, product_id)
);

--create many to many tables:
CREATE TABLE user_company (
	-- just have a standard id?
	id bigserial,
	user_id uuid NOT NULL REFERENCES users (user_id),
	company_id uuid NOT NULL REFERENCES companies (company_id),
	role int
);

CREATE TABLE user_store (
	-- just have a standard id?
	id bigserial,
	user_id uuid NOT NULL REFERENCES users (user_id),
	store_id uuid NOT NULL REFERENCES stores (store_id),
	role int
);

[alteredorange]

Also, it looks like just using bigserial instead of UUID's might be better? Since Citus is handling the sharding, I don't need to worry about colliding BigInts, right?

[marcocitus]

Sorry, we should monitor Discussions section more closely.

How do you shard users, since they can be in multiple companies(tenants)?

Assuming the table does not get a high rate of writes it should be a reference table.

How would you shard the user_store table? Would you track company_id in that table as well?

Yes, it would make sense to distribute it on company_id as well to enable foreign keys to both companies and users.

Also, it looks like just using bigserial instead of UUID's might be better? Since Citus is handling the sharding, I don't need to worry about colliding BigInts, right?

Yes, bigserial works fine.