Which database table table 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 the user and the company are one-to-one.

Answer 1

Of course, the previous one is more effective given this limitation. To get the same information in your requests there will be fewer links.

Answer 2

well, that is a bit of an open question and depends on your business rules. The first option that you have allows only one company to be mapped to one user. you define many-to-one relationships.

The second scheme defines a many-to-many relationship that allows multiple users to be mapped to multiple companies.

They solve different problems and depending on what you are trying to solve, they will determine which circuit you should use.

Strictly speaking, from the “transactional” point of view, the first scheme will be faster, because you only need to fix one line for the user object that will be associated with the company, and only one connection is required to retrieve the company for which your user works, however the second solution will scale better if your business requirements change and require several companies that will trust the user.

Answer 3

As always, it depends. I would personally go with the answer number one, since he would have fewer associations and it would be easier to maintain. Fewer joins should mean less tables and indexes are scanned for this.

 SELECT userid, username, companyid, companyname FROM companies c, users u WHERE userid = companyid 

This is much better than ...

 SELECT userid, username, companyid, companyname FROM companies c, users u, usercompanies uc WHERE u.userid = uc.userid AND c.companyid = uc.companyid 

Answer 4

The two schemes cannot be compared, since they have different relationships, you must look at the specifications for the tables, and then determine which one is suitable for the necessary relations.

The first means that the User can only be a member of one company (relation to_to). While the second scheme implies that the User can be a member of many companies (the has_many relationship)

If you are looking for a scheme that can (or will be later) supporting has_many relationships, then you want to go with the second. For this reason, compare:

  // select all users in company x with schema 1
 select username, companyname from companies
 inner join users on users.companyid = companies.companyid
 where companies.companyid = __some_id__;

and

  // select all users in company x with schema 2
 select username, companyname from companies
 inner join usercompanies on usercompanies.companyid = companies.companyid
 inner join users on usercompanies.userid = users.userid
 where companies.companyid = __some_id__;

You have an additional join in the selection table. If you only need the belongs_to relation, then the second query does more work than it should, and therefore makes it less efficient.

Answer 5

I think you mean many-to-one when it comes to users and companies - unless you plan to have a unique company for each user.

To answer your question, go to the first approach. One less storage table reduces space and forces your queries to use fewer JOIN commands. In addition, and more importantly, it matches your desired input correctly. The database schema should describe the format of all valid data - if it matches the format, it is considered valid. Since a user can have only one company, you may have incorrect data in your database if you use the second scheme.

Answer 6

If the user and the company really have a one-to-one relationship, you only need one table:

 (ID, UserName, CompanyName) 

But I suspect that you really meant that there is one-to-many relationship between the user and the company - one or more user companies, but only one company user. In this case, the two-table solution is correct.

If there is a many-to-many relationship (a company can have several users, and a user can be joined to several companies), then the solution with three tables is correct.

Please note that efficiency is not a problem here. Its the nature of the data that determine which solution you should use.