Tying tables together in MySql

Question

I have one table that includes company information (i.e. name, address, business description...) and another table that has user information (i.e. first name, last name, email and password). How do I tie the company table to the user table so that when the user logs in, the company is properly associated with the user? Would I have a userid column in the company table?

Answer 1

You are looking for joins.

Companies would have a one-to-many relationship with the users, so you need to create a column in the user table with a name like company_id and then query it like this to get users and their company:

SELECT * FROM users INNER JOIN companies ON users.company_id = companies.id

Answer 2

Assuming that several users might belong to a single company but only one company may be associated with a user, a companyid in the user table would make the most sense.

To join two tables together where you expect a match between both in all cases, use an INNER JOIN:

SELECT u.*, c.companyname FROM users u 
INNER JOIN companies c ON u.companyid = c.companyid

Obviously, the above is a simulated query and needs tailoring to match your schema / requirements.

Answer 3

No, you will probably have a one-to-many relation. So one company contains many employees. But one employee belongs to only one company (to keep things simple).

This requires a foreign key in the user table pointing to the id of the company.