Mysql table relation for a user with multiple business

[shizuka]

How to properly implement mysql table relation such that a user can create multiple business and each of those business can have multiple users. thanks

[tahishae]

Here's how I would do it. Create a table for the businesses, and another for the users. I would then create a table that stores the key mapping which tells you which users are associated with each business and vice-versa.

CREATE TABLE Business (BusinessID INT NOT NULL AUTO_INCREMENT, BusinessName VARCHAR(100), PRIMARY KEY (BusinessID));

CREATE TABLE Users (UserID INT NOT NULL AUTO_INCREMENT, Username VARCHAR(100), PRIMARY KEY (UserID));

CREATE TABLE BusinessUsers (BusinessUserID INT NOT NULL AUTO_INCREMENT, UserID INT, BusinessID INT, PRIMARY KEY (BusinessUserID));

You can see it in action by inserting some data in those tables, and then running a simple INNER JOIN to see the results.

INSERT INTO Business (BusinessName) VALUES ('ACME Industries');

INSERT INTO Users (Username) VALUES ('JDOE');

INSERT INTO BusinessUsers (UserID, BusinessID) SELECT B.BusinessID, U.UserID FROM Business B INNER JOIN Users U WHERE U.Username = 'JDOE' AND B.BusinessName = 'ACME Industries'

SELECT B.BusinessName, U.Username FROM Business B INNER JOIN Users U INNER JOIN BusinessUsers BU ON B.BusinessID = BU.BusinessID AND U.UserID = BU.UserID;

You should also have foreign keys between the ID columns on those tables, but you didn't mention which version of MySQL you're using.