0

Hello I started learning some C# and MySQL and I try making 2 or more tables to connect to each other to form a relational database.

I've looked for information and found that some people make there design in WorkBench and some using joins like I've seen in some tutorials on joins.

I am confused about some key concepts that are not very well explained. For example the difference between joins and linktables in WorkBench.

How do they work? Are they the same thing ? Is WorkBench used only for making a design pattern for a database ?

When i say Foreign Key and Primary Key and connect them is this considered as a join or it is a totally different concept and has different uses ?

Boris.stavrev92
  • 21
  • 4

1 Answers1

0

Joins, foreign keys, and primary keys are all closely-related concepts in a relational database.

The Primary Key uniquely identifies rows in a given table.

A Foreign Key denotes a primary key in a different table.

A Join is a way to retrieve data from multiple tables.

I haven't used SQL Workbench much, but my guess is that a link table is referring to a table containing multiple foreign keys to create a "many to many" relationship between two tables.

For example:

Table User

CREATE TABLE User(
    userId INT PRIMARY KEY,
    username VARCHAR(40)
);

Table Post

CREATE TABLE Post(
    postId INT PRIMARY KEY,
    postContents VARCHAR(max)
);

Table UserPost

CREATE TABLE UserPost(
    userId INT FOREIGN KEY REFERENCES User(userId),
    postId INT FOREIGN KEY REFERENCES Post(postId)
);

The table UserPost would be a "link table" that links together the User and Post tables to indicate the many posts that any one user could have.

You could then find all posts of a given user with a SQL query like below, using UserPost to link together User and Post

SELECT u.username, p.postContents FROM User u
  JOIN UserPost up ON up.userId = u.userId
  JOIN Post p ON up.postId = p.postId

So in summary, a link table allows us to use joins to "join" together information from multiple tables. Because of this, link tables are more often referred to as "join tables". See this post for more details about join tables.

clurd
  • 116
  • 1
  • 5