存储关注者和跟踪MySQL的最佳方式

Question

I have 2 tables one is user and another is company. Both have the following columns common while other columns are different.

id   email                name   
1    [e-mail]      First user
2    [e-mail]     Second User
3    [e-mail]      Third User

Now a user can follow many companies but a company does not follow back user. Company should have data with users who follow them and a user should also store the companies they are following.

What is the simplest and optimal to make this happen on MySQL? Either should I create separate table for it or just add a column on existing tables with array of followers. Please answer assuming I have working level of knowledge on PHP and MySQL.

Answer 1

You have the tables User and Company. Let's suppose they contain these columns:

UserId UserName UserMail
------ -------- --------------
1      Alice    [e-mail]
2      Bob      [e-mail]

CompanyId CompanyName CompanyAddress
--------- ----------- --------------
1         Microsoft   Redmond
2         Apple       Cupertino
3         Google      Mountain View

Then you should create a many-to-many table - e.g. let's call it UserCompany - which contains at least the columns UserId and CompanyId (coming from the first two tables).

Let's suppose that Alice follows Microsoft and Apple, and Bob follows Google. Your new UserCompany table will contain the following data:

UserId CompanyId
------ ---------
1      1
1      2
2      3

Company should have data of users who follow them

Here you are the query to get the data of the users that follow Microsoft:

SELECT u.UserName, u.UserMail
FROM User AS u
JOIN UserCompany AS uc ON u.UserId = uc.UserId
WHERE uc.CompanyId = 1

User should also have data of companies they are following.

And here you are the query to get the data of the companies followed by Alice:

SELECT c.CompanyName, c.CompanyCity
FROM UserCompany AS uc
JOIN Company AS c ON c.CompanyId = uc.CompanyId
WHERE uc.UserId = 1