Which database table Schema is more efficient?

Question

Which Database table Schema is more efficient and why?

"Users (UserID, UserName, CompamyId)"
"Companies (CompamyId, CompanyName)"

OR

"Users (UserID, UserName)"
"Companies (CompamyId, CompanyName)"
"UserCompanies (UserID, CompamyId)"

Given the fact that user and company have one-to-one relation.

Answer 1

well that's a bit of an open ended question and depends on your business rules. The first option you have only allows one company to be mapped to one user. you're defining a many-to-one relationship.

The second schema defines a many-to-many relationship which allows multiple users to be mapped to multiple companies.

They solve different problems and depending on what you're trying to solve will determine what schema you should use.

Strictly speaking from a "transactions" point of view, the first schema will be quicker because you only have to commit one row for a user object to be associated to a company and to retrieve the company that your user works for requires only one join, however the second solution will scale better if your business requirements change and require you to have multiple companies assigend to a user.

Answer 2

For sure, the earlier one is more efficient given that constraint. For getting the same information, you will have less number of joins in your queries.