Database Design – Duplicate Company ID vs Inner Join

database-design

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.

Best Answer

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.

Related Question