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)
)
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?