Which Database table Schema is more efficient and why?
哪个数据库表Schema更有效,为什么?
"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.
鉴于用户和公司具有一对一的关系。
6 个解决方案
#1
6
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.
可以肯定的是,考虑到这种约束,前一个更有效。要获取相同的信息,您的查询中的联接数会减少。
#2
7
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.
严格地说,从“事务”的角度来看,第一个模式会更快,因为您只需要为用户对象提交一行以与公司关联,并检索用户工作的公司只需要一个连接,但是,如果您的业务需求发生变化,并且要求您让多个公司分配给用户,则第二个解决方案将更好地扩展。
#3
1
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.
一如既往地取决于。我个人会回答第一个答案,因为它会有更少的连接,并且更容易维护。较少的连接应该意味着它需要较少的表和索引扫描。
SELECT userid, username, companyid, companyname
FROM companies c, users u
WHERE userid = companyid
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
#4
1
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.
这两个模式无法进行比较,因为它们具有不同的关系,您应该准确地查看表格的规范,然后确定哪一个适合所需的关系。
The first one implies that a User can only be a member of one company (a belongs_to relationship). Whereas the second schema implies that a User can be a member of many companies (a has_many relationship)
第一个意味着用户只能是一个公司的成员(belongs_to关系)。而第二个模式暗示用户可以是许多公司的成员(has_many关系)
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:
如果您正在寻找可以(或稍后)支持has_many关系的架构,那么您希望使用第二个架构。因为比较:
//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 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.
您在select表上有一个额外的连接。如果你只想要belongs_to关系,那么第二个查询会比它应该做更多的工作 - 因此效率降低。
#5
0
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.
对于用户和公司而言,我认为你的意思是“多对一” - 除非你打算为每个用户建立一个独特的公司。
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.
要回答您的问题,请使用第一种方法。少存储一个表会减少空间并使查询使用较少的JOIN命令。此外,更重要的是,它正确匹配您所需的输入。数据库模式应描述所有有效数据的格式 - 如果它符合格式,则应视为有效。由于用户只能拥有一家公司,因此如果您使用第二个架构,则数据库中可能存在不正确的数据。
#6
0
If User and Company really have a one-to-one relationship, then you only need one table:
如果用户和公司真的有一对一的关系,那么您只需要一个表:
(ID, UserName, CompanyName)
But I suspect you really meant that there is a one-to-many 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.
但我怀疑你的确意味着用户和公司之间存在一对多的关系 - 一个或多个用户公司,但只有一个公司用户。在这种情况下,双表解决方案是正确的。
If there is a many-to-many relationship (a company can have several users and a user can be attached to several companies), then the three-table solution is correct.
如果存在多对多关系(公司可以拥有多个用户并且用户可以连接到多个公司),那么三表解决方案是正确的。
Note that efficiency is not really the issue here. Its the nature of the data that dictates which solution you should use.
请注意,效率在这里并不是真正的问题。它的数据性质决定了您应该使用哪种解决方案。
#1
6
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.
可以肯定的是,考虑到这种约束,前一个更有效。要获取相同的信息,您的查询中的联接数会减少。
#2
7
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.
严格地说,从“事务”的角度来看,第一个模式会更快,因为您只需要为用户对象提交一行以与公司关联,并检索用户工作的公司只需要一个连接,但是,如果您的业务需求发生变化,并且要求您让多个公司分配给用户,则第二个解决方案将更好地扩展。
#3
1
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.
一如既往地取决于。我个人会回答第一个答案,因为它会有更少的连接,并且更容易维护。较少的连接应该意味着它需要较少的表和索引扫描。
SELECT userid, username, companyid, companyname
FROM companies c, users u
WHERE userid = companyid
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
#4
1
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.
这两个模式无法进行比较,因为它们具有不同的关系,您应该准确地查看表格的规范,然后确定哪一个适合所需的关系。
The first one implies that a User can only be a member of one company (a belongs_to relationship). Whereas the second schema implies that a User can be a member of many companies (a has_many relationship)
第一个意味着用户只能是一个公司的成员(belongs_to关系)。而第二个模式暗示用户可以是许多公司的成员(has_many关系)
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:
如果您正在寻找可以(或稍后)支持has_many关系的架构,那么您希望使用第二个架构。因为比较:
//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 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.
您在select表上有一个额外的连接。如果你只想要belongs_to关系,那么第二个查询会比它应该做更多的工作 - 因此效率降低。
#5
0
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.
对于用户和公司而言,我认为你的意思是“多对一” - 除非你打算为每个用户建立一个独特的公司。
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.
要回答您的问题,请使用第一种方法。少存储一个表会减少空间并使查询使用较少的JOIN命令。此外,更重要的是,它正确匹配您所需的输入。数据库模式应描述所有有效数据的格式 - 如果它符合格式,则应视为有效。由于用户只能拥有一家公司,因此如果您使用第二个架构,则数据库中可能存在不正确的数据。
#6
0
If User and Company really have a one-to-one relationship, then you only need one table:
如果用户和公司真的有一对一的关系,那么您只需要一个表:
(ID, UserName, CompanyName)
But I suspect you really meant that there is a one-to-many 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.
但我怀疑你的确意味着用户和公司之间存在一对多的关系 - 一个或多个用户公司,但只有一个公司用户。在这种情况下,双表解决方案是正确的。
If there is a many-to-many relationship (a company can have several users and a user can be attached to several companies), then the three-table solution is correct.
如果存在多对多关系(公司可以拥有多个用户并且用户可以连接到多个公司),那么三表解决方案是正确的。
Note that efficiency is not really the issue here. Its the nature of the data that dictates which solution you should use.
请注意,效率在这里并不是真正的问题。它的数据性质决定了您应该使用哪种解决方案。