Can a junction table (join table) also be used for a one-to-many relationship?

pez_dispenser picture pez_dispenser · May 9, 2009 · Viewed 7.3k times · Source

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

Answer

j0tt picture j0tt · May 9, 2009

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?