0

Hello i'm designing a database for a saas. Lots of queries will be per company. I have hesitate between 2 way.

First way: Duplicated ids Each table have a FK for the company id and select will contains a simple where on this row.

Second way: Using inner join Each query will have some inner join to get the company id of the user.


  • First way seem cleaner to me, as we don't repeat the relation.
  • Second way seems more performant as there is less join.

Note that there is some tables that will require 5 or six joins to get to the company.

I'm looking for the best option in term of security, clean design, and good performances.

EDIT: Added some examples as I was not that clear.

No many 2 many relation table to simplify

Company <- Contract <- Folder <- Document <- Note
``

Each table point to its parent. If I want all company's documents, or all company's notes, I need to make multiple join if I do not duplicate de company id in all table I must make multiple joins to accomplish this select. 
6
  • You might be better off not using a shared database, it can very quickly become difficult to manage and you limit your ability to perform things like point-in-time recovery for individual tenants. Take a look at this article by Microsoft, it does a good job of explaining the various design patterns for SaaS applications and should help point you in the right direction.
    – HandyD
    Commented Mar 5, 2021 at 3:06
  • @HandyD Thank you Handy, I tought about it, but some specific requirements makes it more complicated.
    – rm4
    Commented Mar 5, 2021 at 3:36
  • ??? In 2nd option - what column wil be used for join? why not company ID?
    – Akina
    Commented Mar 5, 2021 at 4:59
  • @akina I guess they mean "every table has a userid, user table has companyid, should I put companyid in every table too to make life easier when querying so I don't have to join user's table in all the time". - I say "typically, no, we should not put companyid onto every table if it can be got from join to user unless it is solving some performance problem or other legit reason like 'from this document record get all other company document, thousands of times a day' ie joining user's table twice: documents join users on userid join users2 on companyid join documents on userid"
    – Caius Jard
    Commented Mar 5, 2021 at 6:25
  • But even then it seems more logical to have documents owned by a company not a user, and have a M:M between documents and users (multiple authors, reviewers, publishers..). It's case by case basis I think, not a one size fits all answer
    – Caius Jard
    Commented Mar 5, 2021 at 6:28

1 Answer 1

1

One reason for normalization is to smartly decouple a table into multiple tables of related columns so only the needed set of columns are queried at a given time to maximize performance. If the CompanyId is a standard in most of your queries, then from a performance perspective it's not a bad idea to keep it in all relevant tables so you don't have to join multiple unrelated tables to the query, to get the Customer. But from a maintenance perspective it'll be more work to insert the same value into multiple tables as well. So it's a trade-off between performance and maintainability based on the number of tables your system will have.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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