联结表(联接表)是否也可用于一对多关系?

时间:2022-10-05 11:43:38

According to the definition, a Junction Table (bridge table/link table) is used for many-to-many relationships, when used like this:

根据定义,连接表(桥接表/链接表)用于多对多关系,如下所示:

CREATE TABLE Users
(
UserLogin varchar(50) PRIMARY KEY,
UserPassword varchar(50) NOT NULL,
UserName varchar(50) NOT NULL
)


CREATE TABLE Permissions
(
PermissionKey varchar(50) PRIMARY KEY,
PermissionDescription varchar(500) NOT NULL
)


--This is the junction table.
CREATE TABLE UserPermissions
(
UserLogin varchar(50) REFERENCES Users (UserLogin),
PermissionKey varchar(50) REFERENCES Permissions (PermissionKey),
PRIMARY KEY (UserLogin, PermissionKey)
)

But couldn't it also be used just as easily for a one-to-many relationships, as in this example in which one user is associated with many orders:

但是它也不能像一对多关系那样容易使用,就像在这个例子中一个用户与许多订单相关联:

(I don't understand databases well so please correct me if I have misunderstood something.)

(我不太了解数据库,所以如果我误解了某些内容,请纠正我。)

CREATE TABLE Users
(
UserLogin varchar(50) PRIMARY KEY,
UserPassword varchar(50) NOT NULL,
UserName varchar(50) NOT NULL
)


CREATE TABLE Orders
(
OrderKey varchar(50) PRIMARY KEY,
OrderDescription varchar(500) NOT NULL
)


--This is the junction table.
CREATE TABLE UserOrders
(
UserLogin varchar(50) REFERENCES Users (UserLogin),
OrderKey varchar(50) REFERENCES Orders (OrderKey),
PRIMARY KEY (UserLogin, OrderKey)
)

6 个解决方案

#1


6  

Yes, but then you leave the check that it's not many to many to the application, instead of enforcing it inside the database.

是的,但是你要检查它对应用程序来说并不是很多,而不是在数据库中执行它。

#2


8  

There is not any reason why a junction table couldn't be used for a one-to-many relationship. The question is usually one of performance. Why make the database join an additional table when it is unnecessary?

没有任何理由说联结表不能用于一对多关系。问题通常是表现。为什么在不需要时让数据库加入另一个表?

#3


3  

This would be many-to-many:

这可能是多对多的:

CREATE TABLE UserOrders
(UserLogin varchar(50) REFERENCES Users (UserLogin),
OrderKey varchar(50) REFERENCES Orders (OrderKey),
PRIMARY KEY (UserLogin, OrderKey));

This would be one-to-many (one user has many orders):

这将是一对多(一个用户有很多订单):

CREATE TABLE UserOrders
(UserLogin varchar(50) REFERENCES Users (UserLogin),
OrderKey varchar(50) REFERENCES Orders (OrderKey),
PRIMARY KEY (OrderKey));

Note the difference in the PRIMARY KEY constraint.

注意PRIMARY KEY约束的区别。

#4


1  

Once you've built a table, it really doesn't have a type of "Junction" table, "associative" table, "join" table -- it's just a table.

一旦你构建了一个表,它就没有一种“Junction”表,“associative”表,“join”表 - 它只是一个表。

We use these terms to describe a specific reason why an entity (and resulting table) was initially created. Associative entities are created, initially, to resolve a many-to-many situation. But these tables quite often have attributes of their own (such as the time of the association, a reason for the association, etc.). So SQL Server, Oracle or your code has no reason to know why a table was created...just that it's a table.

我们使用这些术语来描述最初创建实体(和结果表)的具体原因。最初创建关联实体以解决多对多情况。但是这些表通常具有自己的属性(例如关联的时间,关联的原因等)。因此,SQL Server,Oracle或您的代码没有理由知道为什么创建表...只是它是一个表。

From a technical point of view, there really isn't any difference between an associative table and any other table.

从技术角度来看,关联表和任何其他表之间确实没有任何区别。

So these tables can fill any role that any other table can fulfill. There are no rules around how other tables can also be related to them.

因此,这些表可以填充任何其他表可以实现的任何角色。没有关于其他表如何与它们相关的规则。

#5


0  

You can enforce de "one" constraint in thee join/junction table adding a unique constraint (or making it the primary key of the join table, because just that atribute itself identifies the relationship) to the column that is a foreign key to the "many" side. That is because you want rwos in the many side have only one relationship and relationships are stated in the join/junction table.

您可以在连接/联结表中强制执行de“one”约束,从而添加一个唯一约束(或使其成为连接表的主键,因为只有该属性本身可以识别关系)到作为“”的外键的列。许多“方面。这是因为你希望许多方面的rwos只有一个关系,并且在join / junction表中声明了关系。

#6


0  

I think you got the concept wrong - Here is the simple explanation if it could help: To achieve a Many-Many relationship between two tables(say, A and B), we need to take the help of a junction table(say, table c) which will have one-many relationship with both tables A and B.

我认为你的概念是错误的 - 这是一个简单的解释,如果它可以帮助:为了实现两个表(比如A和B)之间的多对多关系,我们需要借助一个联结表(比方说,表) c)与表A和B都有一对多的关系。

#1


6  

Yes, but then you leave the check that it's not many to many to the application, instead of enforcing it inside the database.

是的,但是你要检查它对应用程序来说并不是很多,而不是在数据库中执行它。

#2


8  

There is not any reason why a junction table couldn't be used for a one-to-many relationship. The question is usually one of performance. Why make the database join an additional table when it is unnecessary?

没有任何理由说联结表不能用于一对多关系。问题通常是表现。为什么在不需要时让数据库加入另一个表?

#3


3  

This would be many-to-many:

这可能是多对多的:

CREATE TABLE UserOrders
(UserLogin varchar(50) REFERENCES Users (UserLogin),
OrderKey varchar(50) REFERENCES Orders (OrderKey),
PRIMARY KEY (UserLogin, OrderKey));

This would be one-to-many (one user has many orders):

这将是一对多(一个用户有很多订单):

CREATE TABLE UserOrders
(UserLogin varchar(50) REFERENCES Users (UserLogin),
OrderKey varchar(50) REFERENCES Orders (OrderKey),
PRIMARY KEY (OrderKey));

Note the difference in the PRIMARY KEY constraint.

注意PRIMARY KEY约束的区别。

#4


1  

Once you've built a table, it really doesn't have a type of "Junction" table, "associative" table, "join" table -- it's just a table.

一旦你构建了一个表,它就没有一种“Junction”表,“associative”表,“join”表 - 它只是一个表。

We use these terms to describe a specific reason why an entity (and resulting table) was initially created. Associative entities are created, initially, to resolve a many-to-many situation. But these tables quite often have attributes of their own (such as the time of the association, a reason for the association, etc.). So SQL Server, Oracle or your code has no reason to know why a table was created...just that it's a table.

我们使用这些术语来描述最初创建实体(和结果表)的具体原因。最初创建关联实体以解决多对多情况。但是这些表通常具有自己的属性(例如关联的时间,关联的原因等)。因此,SQL Server,Oracle或您的代码没有理由知道为什么创建表...只是它是一个表。

From a technical point of view, there really isn't any difference between an associative table and any other table.

从技术角度来看,关联表和任何其他表之间确实没有任何区别。

So these tables can fill any role that any other table can fulfill. There are no rules around how other tables can also be related to them.

因此,这些表可以填充任何其他表可以实现的任何角色。没有关于其他表如何与它们相关的规则。

#5


0  

You can enforce de "one" constraint in thee join/junction table adding a unique constraint (or making it the primary key of the join table, because just that atribute itself identifies the relationship) to the column that is a foreign key to the "many" side. That is because you want rwos in the many side have only one relationship and relationships are stated in the join/junction table.

您可以在连接/联结表中强制执行de“one”约束,从而添加一个唯一约束(或使其成为连接表的主键,因为只有该属性本身可以识别关系)到作为“”的外键的列。许多“方面。这是因为你希望许多方面的rwos只有一个关系,并且在join / junction表中声明了关系。

#6


0  

I think you got the concept wrong - Here is the simple explanation if it could help: To achieve a Many-Many relationship between two tables(say, A and B), we need to take the help of a junction table(say, table c) which will have one-many relationship with both tables A and B.

我认为你的概念是错误的 - 这是一个简单的解释,如果它可以帮助:为了实现两个表(比如A和B)之间的多对多关系,我们需要借助一个联结表(比方说,表) c)与表A和B都有一对多的关系。