13

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)
)
pez_dispenser
  • 4,394
  • 7
  • 37
  • 47

6 Answers6

11

Yes, it is still possible to store and enforce one-to-many relationship in a junction table.

In your example you are not enforcing any constraints on the UserOrders junction table, so a single order can belong to two users (assuming that's incorrect). To enforce that you could make OrderKey be the primary key of the UserOrders junction table (or have a unique constraint on that column). Technically that will just become a many-to-one relationship between UserOrders and Users, while having one-to-one relationship between Orders and UserOrders.

I can only think about one reason for designing the many-to-one relationship using junction table - if you plan to allow the many-to-many relationship in future and don't want to deal with data migration. But in the mean time you will pay the cost of storing and joining with additional table.

m_vitaly
  • 11,856
  • 5
  • 47
  • 63
  • 1
    Sometimes it can be handy if you want to have the same db tables structure but decide the type of the relation in application: one to many or many to many. (as a parameter) There are times when you develop one to many relation knowing that it's very likely to change to many to many. – m_vitaly May 09 '09 at 18:27
  • I see. And if you wanted to enforce one-to-many or many-to-many in the database, you instead use some type of constraint? – pez_dispenser May 10 '09 at 16:02
  • @jt if you're talking about joint table then you can't enforce one-to-many relation by using only the table. Otherwise you mean enforcing it by "foreign key" contraint. In one to many relation the "many" side has a field with the constraint that means that each row has to point to the "one" side (has one side's id in the foreign-key column and it won't allow another values there). – m_vitaly May 10 '09 at 16:47
  • 4
    In a junction table you can easily enforce one-to-many or one-to-one with a unique constraint on 1 or 2 columns, respectively. – Bart van Heukelom May 11 '15 at 10:04
  • This answer is wrong. A PK/UNIQUE constraint enforces 1:many. – philipxy Aug 30 '18 at 22:09
  • @philipxy that's exactly what the answer is saying, you leave that constraint check to the app instead of enforcing it in the db. Might be useful in some cases where you want to change the app code in future to add support for many to many without changing and migrating the data in the db – m_vitaly Jan 20 '19 at 17:15
  • I don't know what you mean by "that's exactly what the answer is saying", but my comment doesn't agree with your answer or your comment. I agree that "yes" a junction table could "also be used just as easily for a one-to-many". But then one can use DBMS PK/UNIQUE for 1:M. See other answers, eg [nvogel's](https://stackoverflow.com/a/21398351/3404097). – philipxy Jan 21 '19 at 00:48
10

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?

j0tt
  • 1,108
  • 1
  • 7
  • 16
  • 8
    What about in a situation where the foreign key will mostly be NULL? If the junction table, or the association between these tables, is rarely queried or joined, would it then be a better trade off to have a space-saving junction table? – Clint Pachl Sep 15 '15 at 00:18
5

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.

nvogel
  • 24,981
  • 1
  • 44
  • 82
3

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.

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.

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.

Karen Lopez
  • 483
  • 3
  • 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.

opengeek
  • 1
  • 2
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.

higgs_boson
  • 119
  • 1
  • 6