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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2