I'm looking for a second opinion from maybe someone with more experience with SQL.

So I have a database that looks like this :

Company has multiple Clients which has multiple Projects which has multiple Tasks, etc.

In my application a user is assigned a company and cannot query information that isn't tied to it. So whenever a user tries to retrieve Client/Project/Task/Punch I need to make sure that my query contains a Where clause that looks like WHERE companyID=[user's company id]. This add a lot of joins when I need to fetch Punch since I need to go up the chain to see if the company is the same as the user.

Since a client/project/task/punch will never switch from a company to another one, I was wondering if there's any red flag to add a companyID field in project/task/punch in order to simplify the querying ?

I'm using PostgreSQL

CodePudding user response：

If I understand correctly, what you are buildng is a multitenant system, where your companies are the tenants. If that is the case then there are no red flags - on the contrary, your main concern is to isolate data belonging to different companies in the most efficient and most secure way.

I find this old blog post to be a basic but clear introduction to multitenancy.

The recommended way to go was then, and is today, the third option: one DB, many schemas. I'm no Postgres expert, but I believe it supports that option quite well.