Mysql table relation for a user with multiple business
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
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.