<p>Which Database table Schema is more efficient and why?</p>

<pre><code>"Users (UserID, UserName, CompamyId)"
"Companies (CompamyId, CompanyName)"
</code></pre>

<p>OR</p>

<pre><code>"Users (UserID, UserName)"
"Companies (CompamyId, CompanyName)"
"UserCompanies (UserID, CompamyId)"
</code></pre>

<p>Given the fact that user and company have one-to-one relation.</p>

[{'answer_id': 38793, 'author': 'Vaibhav', 'author_id': 380, 'author_profile': 'https://Stackoverflow.com/users/380', 'pm_score': 4, 'selected': True, 'text': '<p>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.</p>\n'}, {'answer_id': 38795, 'author': 'lomaxx', 'author_id': 493, 'author_profile': 'https://Stackoverflow.com/users/493', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n\n<p>The second schema defines a many-to-many relationship which allows multiple users to be mapped to multiple companies. </p>\n\n<p>They solve different problems and depending on what you\'re trying to solve will determine what schema you should use.</p>\n\n<p>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.</p>\n'}, {'answer_id': 38796, 'author': 'Kevin Lamb', 'author_id': 3149, 'author_profile': 'https://Stackoverflow.com/users/3149', 'pm_score': 1, 'selected': False, 'text': '<p>As always it depends. I would personally go with answer number one since it would have less joins and would be easier to maintain. Less joins should mean that it requires less table and index scans.</p>\n\n<pre><code>SELECT userid, username, companyid, companyname\nFROM companies c, users u\nWHERE userid = companyid\n</code></pre>\n\n<p>Is much better than...</p>\n\n<pre><code>SELECT userid, username, companyid, companyname\nFROM companies c, users u, usercompanies uc\nWHERE u.userid = uc.userid\nAND c.companyid = uc.companyid\n</code></pre>\n'}, {'answer_id': 38798, 'author': 'Kyle Cronin', 'author_id': 658, 'author_profile': 'https://Stackoverflow.com/users/658', 'pm_score': 0, 'selected': False, 'text': '<p>I think you mean "many to one" when it comes to users and companies - unless you plan on having a unique company for each user.</p>\n\n<p>To answer your question, go with the first approach. One less table to store reduces space and will make your queries use less JOIN commands. Also, and more importantly, it correctly matches your desired input. The database schema should describe the format for all valid data - if it fits the format it should be considered valid. Since a user can only have one company it\'s possible to have incorrect data in your database if you use the second schema.</p>\n'}, {'answer_id': 38799, 'author': 'roo', 'author_id': 716, 'author_profile': 'https://Stackoverflow.com/users/716', 'pm_score': 1, 'selected': False, 'text': '<p>The two schemas cannot be compared, as they have different relationships, you should proablly look at what the spec is for the tables and then work out which one fits the relationship needed.</p>\n\n<p>The first one implies that a User can only be a member of <em>one</em> company (a belongs_to relationship). Whereas the second schema implies that a User can be a member of many companies (a has_many relationship)</p>\n\n<p>If you are looking for a schema that can (or will later) support a has_many relationship then you want to go with the second one. For the reason compare:</p>\n\n<pre>\n//select all users in company x with schema 1\nselect username, companyname from companies\ninner join users on users.companyid = companies.companyid\nwhere companies.companyid = __some_id__;\n</pre>\n\n<p>and</p>\n\n<pre>\n//select all users in company x with schema 2\nselect username, companyname from companies\ninner join usercompanies on usercompanies.companyid = companies.companyid\ninner join users on usercompanies.userid = users.userid\nwhere companies.companyid = __some_id__;\n</pre>\n\n<p>You have an extra join on the select table. If you only want the belongs_to relationship then the second query does more work than it should - and so makes it less efficient.</p>\n'}, {'answer_id': 83365, 'author': 'JacquesB', 'author_id': 7488, 'author_profile': 'https://Stackoverflow.com/users/7488', 'pm_score': 0, 'selected': False, 'text': '<p>If User and Company really have a <strong>one-to-one</strong> relationship, then you only need one table:</p>\n\n<pre><code>(ID, UserName, CompanyName)\n</code></pre>\n\n<p>But I suspect you really meant that there is a <em>one-to-many</em> relationship between user and company - one or more users pr company but only one company pr user. In that case the two-table solution is correct. </p>\n\n<p>If there is a <em>many-to-many</em> relationship (a company can have several users and a user can be attached to several companies), then the three-table solution is correct.</p>\n\n<p>Note that <em>efficiency</em> is not really the issue here. Its the nature of the data that dictates which solution you should use.</p>\n'}]
['https://Stackoverflow.com/questions/38791', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/191/']