MySQL Basic about relationship

Question

Hi all!

My doubt is about how to treat the follow thing:

I have a system where a user belong to a company, and this user have their clients.

How is the right way to get a list of all company clients and the follow user name??

In the client table where i have a field with the one of this relations:

• A company_id and user_id field
• Just company_id field
• Just user_id field cause user table have the company_id???
• Something else...

Tkz Roberto

Answer 1

You could use a select statement

SELECT company_id,user_id FROM client;

Answer 2

Tables relations:
Client (FK_companyId, FK_userId). "use FK_companyId only if you have multi-companies"
User (FK_companyId).
company (NO foreign keys for client or user).

if there is ONLY one company in the system then you don't need to include it in the relation:

SELECT clientInfo FROM client where userId=userSessionId;

if you have multi-companies then:
SELECT client.clientInfo,client.companyId,company.companyInfo FROM client left join company on (client.companyId = company.Id) where userId=userSessionId;

Note: the left join used to get the "company info" if its available but all user clients linked to that particular company will be retrieved.

Finally: If one client info can be managed by mutli users then you shall not link/couple the the two entities together.

BTW: your English is horrible!