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 first@email.com First user
2 second@email.com Second User
3 third@email.com 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.
You have the tables User and Company. Let's suppose they contain these columns:
UserId UserName UserMail
------ -------- --------------
1 Alice alice@mail.com
2 Bob bob@mail.com
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
The simplest solution is to have a MANY-TO-MANY join table to link these 2 tables. You can do this with either 2 MANY-TO-MANY tables or a single one.
For example 2 table:
user_follows_company
ID CompanyID UserID
---- ------ ---------
1 1 5
1 1 6
1 1 8
1 2 5
You can get the companies a user follows by using the following SQL:
SELECT * FROM company c JOIN user_follows_company ufc on ufc.companyID = c.ID WHERE user = $USER_ID
company_follows_user
ID CompanyID UserID
---- --------- ---------
1 3 5
1 3 6
1 5 3
1 5 4
You can get the users a company follows by using the following SQL:
SELECT * FROM user u JOIN company_follow_user cfu on cfu.userID = u.ID WHERE user = $USER_ID
or you can do this with a single table and have a column that designates which direction is being followed:
follows_table
ID CompanyID UserID Initiator
---- --------- --------- ---------
1 3 5 Company
1 3 6 Company
1 5 3 Company
1 5 4 Company
1 5 1 User
1 6 1 User
1 8 1 User
1 5 2 User
Note that storing the "initiator" as a string is not a good idea - it probably should be an int or an ENUM
To query on this table, do the following queries:
Companies a user follows: SELECT * FROM company c JOIN follows_table ft on ft.userID = c.ID WHERE user = $USER_ID AND ft.initiator = 'user'
Users a company follows: SELECT * FROM user u JOIN follows_table ft on ft.userID = u.ID WHERE companyID = $$COMPANY_ID AND ft.initiator = 'company'